package com.chinacoal.microservice.service.impl;

import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSONObject;
import com.auth0.jwt.JWT;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.chinacoal.microservice.mapper.*;
import com.chinacoal.microservice.mq.MessageSend;
import com.chinacoal.microservice.pojo.Currency;
import com.chinacoal.microservice.pojo.*;
import com.chinacoal.microservice.service.CostManagementService;
import com.chinacoal.microservice.timedTasks.GenerateExpenseInformationTask;
import com.chinacoal.microservice.util.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


/**
 * @author zhuliang
 * @version V1.0
 * @Description: 运维模块-费用管理-后端实现类
 * @date 2020-11-25 20：21
 */
@Slf4j
@Service
public class CostManagementServiceImpl implements CostManagementService {

    @Autowired
    private CcmsSmartleasingExpenseManagMapper expenseManagMapper;

    @Autowired
    private CcmsSmartleasingNoticeMapper noticeMapper;

    @Autowired
    private CcmsSmartleasingExpenseEntryMapper expenseEntryMapper;

    @Autowired
    private CcmsSmartleasingInvoiceRecordMapper invoiceRecordMapper;

    @Autowired
    private CcmsSmartleasingTenantMapper tenantMapper;

    @Autowired
    private BasicConfiguration basicConfiguration;

    @Autowired
    private CcmsSmartleasingHistoricalPaymentMapper historicalPaymentMapper;

    @Autowired
    private CcmsSmartleasingCoefficientTemplateMapper coefficientTemplateMapper;

    @Autowired
    private CcmsSmartleasingRegionalAnalysisMapper regionalAnalysisMapper;

    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private MessageSend messageSend;

    @Autowired
    private CcmsSmartleasingRoomMapper roomMapper;

    @Autowired
    private CcmsSmartleasingExpenseLockMapper expenseLockMapper;

    @Autowired
    private GenerateExpenseInformationTask generateExpenseInformationTask;

    @Autowired
    private CcmsSmartleasingExpenseReportMapper expenseReportMapper;

    @Autowired
    private static final String EM_NEED_PAY = "0" ; //待缴费
    @Autowired
    private static final String EM_PAID = "1" ; //已缴费
    @Autowired
    private static final String ONLINE_PAY = "0" ; //在线缴费
    @Autowired
    private static final String OFFLINE_PAY = "1" ; //线下缴费
    @Autowired
    private static final String TM_PAID = "0";  //租户：正常缴费
    @Autowired
    private  static final String TM_NEED_PAY = "1"; //租户：欠费

    @Autowired
    private CcmsSmartleasingContractMapper contractMapper;

    @Value("${msg.expense.url}")
    private String msgUrl;

    /**
     * @Description: 查询所有的费用信息(带分页)
     * @author zhuliang
     * @date 2020/11/27 9:51
     */
    @Override
    public Result<List<CcmsSmartleasingExpenseManag>> getAllByExpenseManag(QueryCriteria queryCriteria, String token) {
        //得到登录人信息
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //设置分页查询条件
        Page<CcmsSmartleasingExpenseManag> toPage = new Page<>(queryCriteria.getPage(), queryCriteria.getPageSize());

        //开始分页查询
        List<CcmsSmartleasingExpenseManag> expenseManags = expenseManagMapper.getAllByPage(toPage, currency.getProperty(), currency.getManagementOffice());

        return Result.success(expenseManags, toPage.getTotal());
    }

    /**
     * @Description: 条件查询所有的费用信息(带分页)
     * @author zhuliang
     * @date 2020/11/27 10:37
     */
    @Override
    public Result<List<CcmsSmartleasingExpenseManag>> getAllExpenseManagByCondition(QueryCriteria queryCriteria, String token) {
        //得到登录人信息
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //设置分页查询条件
        Page<CcmsSmartleasingExpenseManag> toPage = new Page<>(queryCriteria.getPage(), queryCriteria.getPageSize());

        //开始条件查询
        List<CcmsSmartleasingExpenseManag> expenseManagList = expenseManagMapper.getAllByCondition(queryCriteria.getHouseNumber(),
                queryCriteria.getUnitCoding(),
                queryCriteria.getPaymentStatus(),
                queryCriteria.getPaymentTime(),
                queryCriteria.getInputString(),
                queryCriteria.getInvoiceStatus(),
                queryCriteria.getFkLessorId(),
                queryCriteria.getFkBuildingId(),
                queryCriteria.getContactPhone(),
                queryCriteria.getStatisticsMonth(),
                currency.getProperty(),
                currency.getManagementOffice(),
                toPage);

        return Result.success(expenseManagList, toPage.getTotal());
    }


    /**
     * @Description: 通过费用信息id查询费用详情
     * @author liule
     * @date 2022/10/09
     */
    @Override
    public Result<List<CcmsSmartleasingHistoricalPayment>> getHistoricalPaymentsByExpenseManagId(String fkExpenseManagId,String token) {
        //得到登录人信息
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //根据id查询
        List<CcmsSmartleasingHistoricalPayment> historicalPayments = historicalPaymentMapper.getHistoricalPaymentsByExpenseManagId(fkExpenseManagId);
        //兼容性处理
        if (null == historicalPayments) {
            log.error(CodeMsg.NOT_FOUND.toString() + "-->此费用信息无权限访问，或数据丢失-->" + fkExpenseManagId);
            return Result.error(CodeMsg.NOT_FOUND);
        }

        return Result.success(historicalPayments);
    }

    /**
     * @Description: 通过费用信息id查询费用详情
     * @author zhuliang
     * @date 2020/11/27 11:35
     */
    @Override
    public Result<CcmsSmartleasingNotice> getNoticeByContractId(String fkExpenseManagId, String token) {
        //得到登录人信息
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //根据id查询
//        CcmsSmartleasingNotice notice = noticeMapper.getNoticeByExpenseManagId(fkExpenseManagId, currency.getProperty(), currency.getManagementOffice());
        CcmsSmartleasingNotice notice = noticeMapper.getNewNoticeByExpenseManagId(fkExpenseManagId, currency.getProperty(), currency.getManagementOffice());
        //兼容性处理
        if (null == notice) {
            log.error(CodeMsg.NOT_FOUND.toString() + "-->此费用信息无权限访问，或数据丢失-->" + fkExpenseManagId);
            return Result.error(CodeMsg.NOT_FOUND);
        }

        return Result.success(notice);
    }

    /**
     * @Description: 通过费用信息id得到要抄表所需要回显的信息
     * @author zhuliang
     * @date 2020/11/27 14:01
     */
    @Override
    public Result<CcmsSmartleasingExpenseManag> meterReadingByExpenseManagId(String pkExpenseManagId, String token) {
        //得到登录人信息
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //通过id得到回显信息
        CcmsSmartleasingExpenseManag meterReadingByExpenseManagId = expenseManagMapper.getMeterReadingByExpenseManagId(pkExpenseManagId,
                currency.getProperty(),
                currency.getManagementOffice());

        //通过费用id去查询模板
        CcmsSmartleasingCoefficientTemplate ccmsSmartleasingCoefficientTemplate = coefficientTemplateMapper.selectGarbageDisposalFee(pkExpenseManagId);
        //根据费用id查询绑定房间号的建筑面积
        BigDecimal constructionArea = coefficientTemplateMapper.selectConstructionAreaByExpenseManagId(pkExpenseManagId);
        //回填电梯费单价
        if (ccmsSmartleasingCoefficientTemplate.getUnitPriceElevatorFee() != null) {
            if (constructionArea != null) {
                BigDecimal elevatorFee = constructionArea.multiply(ccmsSmartleasingCoefficientTemplate.getUnitPriceElevatorFee());
                meterReadingByExpenseManagId.setElevatorFee(elevatorFee);
            }
        }
        //回填卫生费
        if (ccmsSmartleasingCoefficientTemplate.getSanitationFee() != null) {
            meterReadingByExpenseManagId.setSanitationFee(ccmsSmartleasingCoefficientTemplate.getSanitationFee());
        }
        //回填本体维修基金
        if (ccmsSmartleasingCoefficientTemplate.getMaintenanceFund() != null) {
            meterReadingByExpenseManagId.setMaintenanceFund(ccmsSmartleasingCoefficientTemplate.getMaintenanceFund());
        }
        //回显电视费/煤保
        if (ccmsSmartleasingCoefficientTemplate.getGasInsurance() != null) {
            meterReadingByExpenseManagId.setGasInsurance(ccmsSmartleasingCoefficientTemplate.getGasInsurance());
        }
        //兼容性处理
        if (null == meterReadingByExpenseManagId) {
            log.error(CodeMsg.NOT_FOUND.toString() + "-->此费用信息无权限访问，或数据丢失-->" + pkExpenseManagId);
            return Result.error(CodeMsg.NOT_FOUND);
        }

        //查询云丁设备读数
        BigDecimal deviceReading = expenseManagMapper.getWaterReading(meterReadingByExpenseManagId.getFkRoomId());
        if (null != deviceReading){
            meterReadingByExpenseManagId.setMonthWaterMobility(deviceReading);
        }
        deviceReading = expenseManagMapper.getElectricReading(meterReadingByExpenseManagId.getFkRoomId());
        if (null != deviceReading) {
            meterReadingByExpenseManagId.setMonthElectricityMobility(deviceReading);
        }
        deviceReading = expenseManagMapper.getHotWaterReading(meterReadingByExpenseManagId.getFkRoomId());
        if (null != deviceReading) {
            meterReadingByExpenseManagId.setMonthHotWaterMobility(deviceReading);
        }

        return Result.success(meterReadingByExpenseManagId);
    }

    /**
     * @Description: 录入抄表信息
     * @author zhuliang
     * @date 2020/12/1 1:09
     */
    @Transactional
    @Override
    public Result addMeterReadingInformation(CcmsSmartleasingExpenseEntry expenseEntry, String token) {
        //得到登录人id
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //得到对应的通知单信息和费用列表信息
        CcmsSmartleasingNotice notice = noticeMapper.getAllByExpenseManagId(expenseEntry.getFkRoomId(), expenseEntry.getFkExpenseManagId());
        CcmsSmartleasingExpenseManag expenseManag = expenseManagMapper.selectById(expenseEntry.getFkExpenseManagId());
        //将抄表信息写入数据库
        expenseEntry.setLastUpdatedBy(currency.getLoginId());
        expenseEntry.setLastUpdateDate(new Date());
        if (expenseEntry.getElevatorFee()==null){
            //将抄表信息电梯费置为空
            expenseEntryMapper.updateElevatorFeeToNull(expenseEntry.getPkExpenseEntryId());
        }
        if (expenseEntry.getSanitationFee()==null){
            //将抄表信息卫生费置为空
            expenseEntryMapper.updateSanitationFeeToNull(expenseEntry.getPkExpenseEntryId());
        }
        if (expenseEntry.getMaintenanceFund()==null){
            //将抄表信息本体维修基金置为空
            expenseEntryMapper.updateMaintenanceFundToNull(expenseEntry.getPkExpenseEntryId());
        }
        if (expenseEntry.getGasInsurance()==null){
            //将电视费/煤保置为空
            expenseEntryMapper.updateGasInsuranceToNull(expenseEntry.getPkExpenseEntryId());
        }
        expenseEntryMapper.updateById(expenseEntry);

        //修改通知单数据，并写入数据库
        if (StringUtils.isNotBlank(notice.getWaterCostCode())) {
            //本月行度（水费）
            notice.setMonthWaterMobility(expenseEntry.getMonthWaterMobility());
            //上月行度（水费）
            notice.setLastMonthWaterMobility(expenseEntry.getLastMonthWaterMobility());
            //水用量 = 本月行度 - 上月行度
            BigDecimal monthWaterConsumption = expenseEntry.getMonthWaterConsumption();
            notice.setWaterConsumption(monthWaterConsumption);
            //水损量 = 水用量 * 水损系数
            notice.setWaterLossConsumption(notice.getWaterConsumption().multiply(notice.getWaterLossCoefficient()).setScale(2, RoundingMode.HALF_UP));
            //实际水量方便后面计算
            BigDecimal actualWater = monthWaterConsumption.add(notice.getWaterLossConsumption());
            //计算水费  水费 =（水用量 + 水损量）* 分段水价   计算排水费  排水费 =（水用量 + 水损量）* 排水费系数 * 分段排水费水价
            BigDecimal waterCharges;    //水费
            BigDecimal drainageFee;     //排水费
            BigDecimal lessThanWater = new BigDecimal(notice.getLessThanWater());
            BigDecimal greaterThanWater = new BigDecimal(notice.getGreaterThanWater());
            BigDecimal pressureAdministrationCosts;
            CcmsSmartleasingCoefficientTemplate ccmsSmartleasingCoefficientTemplate = coefficientTemplateMapper.selectGarbageDisposalFee(expenseEntry.getFkExpenseManagId());
            if (ccmsSmartleasingCoefficientTemplate != null) {
                if (actualWater.compareTo(new BigDecimal(0)) == 0) {
                    //水用量为0时
                    waterCharges = new BigDecimal(0);
                    //水用量为0,排水费也为0
                    drainageFee = new BigDecimal(0);
                    //水用量为0,加压管理费也为0
                    pressureAdministrationCosts = new BigDecimal(0);
                } else if (actualWater.compareTo(lessThanWater) == -1) {
                    //小于最低水吨数，直接计算
                    waterCharges = actualWater.multiply(notice.getLessUnitPriceOfWater()).setScale(2, RoundingMode.HALF_UP);
                    //排水费计算
                    drainageFee = actualWater.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getLessUnitPriceDrainageFee()).setScale(2, RoundingMode.HALF_UP);
                    //加压管理费计算 加压管理费 = 水用量 * 加压系数 * 分段水价
                    pressureAdministrationCosts = monthWaterConsumption.multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getLessUnitPriceOfWater());
                } else if (actualWater.compareTo(greaterThanWater) < 1) {
                    //计算水量
                    BigDecimal subtract = actualWater.subtract(lessThanWater);
                    //小于等于最高水吨数
                    waterCharges = lessThanWater.multiply(notice.getLessUnitPriceOfWater());
                    waterCharges = waterCharges.add(subtract.multiply(notice.getMiddleUnitPriceOfWater())).setScale(2, RoundingMode.HALF_UP);
                    //排水费计算
                    drainageFee = lessThanWater.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getLessUnitPriceDrainageFee());
                    drainageFee = drainageFee.add(subtract.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getMiddleUnitPriceDrainageFee())).setScale(2, RoundingMode.HALF_UP);
                    //加压管理费计算 加压管理费 = 水用量 * 加压系数 * 分段水价
                    pressureAdministrationCosts = lessThanWater.multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getLessUnitPriceOfWater());
                    pressureAdministrationCosts = pressureAdministrationCosts.add((monthWaterConsumption.subtract(lessThanWater)).multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getMiddleUnitPriceOfWater()));
                } else {
                    //计算水量
                    BigDecimal middle = greaterThanWater.subtract(lessThanWater);
                    BigDecimal subtract = actualWater.subtract(greaterThanWater);
                    //大于最高水吨数
                    waterCharges = lessThanWater.multiply(notice.getLessUnitPriceOfWater());
                    waterCharges = waterCharges.add(middle.multiply(notice.getMiddleUnitPriceOfWater()));
                    waterCharges = waterCharges.add(subtract.multiply(notice.getGreaterUnitPriceOfWater())).setScale(2, RoundingMode.HALF_UP);
                    //排水费计算
                    drainageFee = lessThanWater.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getLessUnitPriceDrainageFee());
                    drainageFee = drainageFee.add(middle.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getMiddleUnitPriceDrainageFee()));
                    drainageFee = drainageFee.add(subtract.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getGreaterUnitPriceDrainageFee())).setScale(2, RoundingMode.HALF_UP);
                    //加压管理费计算 加压管理费 = 水用量 * 加压系数 * 分段水价
                    pressureAdministrationCosts = lessThanWater.multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getLessUnitPriceOfWater());
                    pressureAdministrationCosts = pressureAdministrationCosts.add(middle.multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getMiddleUnitPriceOfWater()));
                    pressureAdministrationCosts = pressureAdministrationCosts.add((monthWaterConsumption.subtract(greaterThanWater)).multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getGreaterUnitPriceOfWater()));
                }
                //加压管理费 = 水用量 * 加压系数 * 分段水价
                notice.setPressureAdministrationCosts(pressureAdministrationCosts);
                //排水费 = （水用量 + 水损量）* 排水费系数 * 分段排水费水价
                notice.setDrainageFee(drainageFee);
                //水费税金 = （水费 + 加压管理费） * 水费税率
                notice.setWaterTariff((waterCharges.add(notice.getPressureAdministrationCosts())).multiply(ccmsSmartleasingCoefficientTemplate.getWaterTariffRate()).setScale(2, RoundingMode.HALF_UP));
                //水小计 = 水费 + 水费税金 + 加压管理费
                notice.setWaterRentSubtotal(waterCharges.add(notice.getWaterTariff()).add(notice.getPressureAdministrationCosts()));
            }
        }

        // hot water processing
        if (StringUtils.isNotBlank(notice.getHotWaterCostCode())) {
            notice.setMonthHotWaterMobility(expenseEntry.getMonthHotWaterMobility());
            notice.setLastMonthHotWaterMobility(expenseEntry.getLastMonthHotWaterMobility());
            BigDecimal monthHotWaterConsumption = expenseEntry.getMonthHotWaterConsumption();
            notice.setHotWaterConsumption(monthHotWaterConsumption);
            notice.setHotWaterRentSubtotal(monthHotWaterConsumption.multiply(notice.getUnitPriceHotWater()));
        }

        if (StringUtils.isNotBlank(notice.getElectricCostCode())) {
            //本月行度（电费）
            notice.setMonthElectricityMobility(expenseEntry.getMonthElectricityMobility());
            //上月行度（电费）
            notice.setLastMonthElectricityMobility(expenseEntry.getLastMonthElectricityMobility());
            //公共用电分摊 =（本月行度-上月行度）* 电损系数
            notice.setPublicElectricitySharing(expenseEntry.getMonthElectricityConsumption().multiply(notice.getElectricLossCoefficient()).setScale(2, RoundingMode.HALF_UP));
            //实际用量 = （本月行度-上月行度）+ 公共用电分摊
            BigDecimal electricActualConsumption = expenseEntry.getMonthElectricityConsumption().add(notice.getPublicElectricitySharing());
            notice.setElectricActualConsumption(electricActualConsumption);
            //电费 = （实际用量）* 分段电价
            BigDecimal electricityFees;
            BigDecimal lessElectricityConsumption = new BigDecimal(notice.getLessElectricityConsumption());
            BigDecimal greaterElectricityConsumption = new BigDecimal(notice.getGreaterElectricityConsumption());
            if (electricActualConsumption.compareTo(new BigDecimal(0)) == 0) {
                //电用量为0时
                electricityFees = new BigDecimal(0);
            } else if (electricActualConsumption.compareTo(lessElectricityConsumption) == -1) {
                //小于最低电用量，直接计算
                electricityFees = electricActualConsumption.multiply(notice.getLessUnitPriceOfElectricity()).setScale(2, RoundingMode.HALF_UP);
            } else if (electricActualConsumption.compareTo(greaterElectricityConsumption) < 1) {
                //小于等于最高电用量
                electricityFees = lessElectricityConsumption.multiply(notice.getLessUnitPriceOfElectricity());
                electricityFees = electricityFees.add((electricActualConsumption.subtract(lessElectricityConsumption)).multiply(notice.getMiddleUnitPriceOfElectricity())).setScale(2, RoundingMode.HALF_UP);
            } else {
                //大于最高电用量
                electricityFees = lessElectricityConsumption.multiply(notice.getLessUnitPriceOfElectricity());
                electricityFees = electricityFees.add((greaterElectricityConsumption.subtract(lessElectricityConsumption)).multiply(notice.getMiddleUnitPriceOfElectricity()));
                electricityFees = electricityFees.add((electricActualConsumption.subtract(greaterElectricityConsumption)).multiply(notice.getGreaterUnitPriceOfElectricity())).setScale(2, RoundingMode.HALF_UP);
            }
            //电小计 = 电费
            notice.setElectricRentSubtotal(electricityFees);
        }
        if (StringUtils.isNotBlank(notice.getCoalGasCostCode())) {
            //本月行度（煤气）
            notice.setMonthGasMobility(expenseEntry.getMonthGasMobility());
            //上月行度（煤气）
            notice.setLastGasMobility(expenseEntry.getLastMonthGasMobility());
            //本月用气量=本月表数-上月表数
            notice.setMonthGasConsumption(expenseEntry.getMonthGasConsumption());
            //欠费
            notice.setGasArrears(expenseEntry.getGasArrears());
            //换表费
            notice.setGasChangeWatchSpense(expenseEntry.getGasChangeWatchSpense());
            //气费=抄表录入
            notice.setGasShortage(expenseEntry.getGasShortage());
            //小计=气费+换表费+欠费
            notice.setGasRentSubtotal(notice.getGasShortage().add(notice.getGasArrears()).add(notice.getGasChangeWatchSpense()));
        }
        //其他 = 抄表录入
        notice.setOtherPrice(expenseEntry.getOtherPrice());
        //电梯费 = 抄表录入
        if (expenseEntry.getElevatorFee() != null){
            notice.setElevatorRate(expenseEntry.getElevatorFee());
        } else {
            noticeMapper.updateElevatorFeeToNull(notice.getPkNoticeId());
        }
        //卫生费 = 抄表录入
        if (expenseEntry.getSanitationFee() != null){
            notice.setSanitationFee(expenseEntry.getSanitationFee());
        } else {
            noticeMapper.updateSanitationFeeToNull(notice.getPkNoticeId());
        }
        //本体维修基金 = 抄表录入
        if (expenseEntry.getMaintenanceFund() != null){
            notice.setMaintenanceFund(expenseEntry.getMaintenanceFund());
        } else {
            noticeMapper.updateMaintenanceFundToNull(notice.getPkNoticeId());
        }
        //电视费/煤保 = 抄表录入
        if (expenseEntry.getGasInsurance() != null){
            notice.setGasInsurance(expenseEntry.getGasInsurance());
        } else {
            noticeMapper.updateGasInsuranceToNull(notice.getPkNoticeId());
        }
        //综合管理费 = 抄表录入
        notice.setComprehensiveManagementRate(expenseEntry.getComprehensiveManagementRate());
        //根据费用ID去模板里获取垃圾处理费
        CcmsSmartleasingCoefficientTemplate ccmsSmartleasingCoefficientTemplate = coefficientTemplateMapper.selectGarbageDisposalFee(expenseEntry.getFkExpenseManagId());
        BigDecimal garbageDisposalFee = ccmsSmartleasingCoefficientTemplate.getGarbageDisposalFee();
        if (garbageDisposalFee != null && garbageDisposalFee.compareTo(new BigDecimal(0)) > 0) {
            notice.setTrashHandlingFee(garbageDisposalFee);
        } else {
            //垃圾处理费=取整【（用水量（水费本月行度-水费上月行度）+水损量）*垃圾处理费倍率*排水费系数】*垃圾处理费单价
            if (null != notice.getWaterConsumption() && !notice.getWaterConsumption().equals(new BigDecimal(0))) {
                //计算用量
                BigDecimal consumption = notice.getWaterConsumption().add(notice.getWaterLossConsumption());
                BigDecimal bigDecimal = consumption.multiply(notice.getGarbageTreatmentMagnification()).multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP);
                notice.setTrashHandlingFee(bigDecimal.multiply(notice.getUnitPriceGarbageTreatment()));
            } else {
                notice.setTrashHandlingFee(new BigDecimal(0));
            }
        }
        //其他费用小计 = 房租 + 综合管理费 + 排水费 + 垃圾处理费 + 电话费 + 电梯费 + 卫生费 + 本体维修基金 + 电视费/煤保 + 其他
        notice.setOtherSubtotal(notice.getMonthlyRent().add(notice.getTrashHandlingFee()).add(notice.getOtherPrice()).add(notice.getComprehensiveManagementRate()));
        if (null != notice.getDrainageFee()) {
            // 排水费
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getDrainageFee()));
        }
        if (null != notice.getTelephoneRate()) {
            // 电话费
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getTelephoneRate()));
        }
        if (null != notice.getSanitationFee()) {
            // 卫生费
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getSanitationFee()));
        }
        if (null != notice.getElevatorRate()) {
            // 电梯费
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getElevatorRate()));
        }
        if (null != notice.getGasInsurance()) {
            // 电视费/煤保
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getGasInsurance()));
        }
        if (null != notice.getMaintenanceFund()){
            // 本体维修基金
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getMaintenanceFund()));
        }

        //本月应收总额=水费小计+电费小计+煤气费小计+其他费用小计
        notice.setTotalAmount(notice.getOtherSubtotal());
        if (null != notice.getWaterRentSubtotal()) {
            notice.setTotalAmount(notice.getTotalAmount().add(notice.getWaterRentSubtotal()));
        }
        if (null != notice.getElectricRentSubtotal()) {
            notice.setTotalAmount(notice.getTotalAmount().add(notice.getElectricRentSubtotal()));
        }
        if (null != notice.getGasRentSubtotal()) {
            notice.setTotalAmount(notice.getTotalAmount().add(notice.getGasRentSubtotal()));
        }


        //应收费用
        notice.setExpensesReceivable(notice.getTotalAmount());

        //修改信息
        notice.setLastUpdateDate(new Date());
        notice.setLastUpdatedBy(currency.getLoginId());

        noticeMapper.updateById(notice);

        //修改费用列表信息数据，并写入数据库
        expenseManag.setLastUpdatedBy(currency.getLoginId());
        expenseManag.setLastUpdateDate(new Date());
        expenseManag.setMeterReadingStatus("1");
        expenseManag.setMeterReadingTime(new Date());
        expenseManag.setTotalAmount(notice.getTotalAmount());
        expenseManag.setExpensesReceivable(notice.getTotalAmount());
        expenseManagMapper.updateById(expenseManag);

        //发送账单审核通知消息
        CloseableHttpClient httpClient = HttpClientBuilder.create().build();
        HttpGet httpGet = new HttpGet(msgUrl);
        try {
            httpClient.execute(httpGet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 释放资源
                if (httpClient != null) {
                    httpClient.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return Result.success();
    }

    /**
     * @Description: 缴费
     * @author zhuliang
     * @date 2020/12/4 18:00
     */
    @Transactional
    @Override
    public Result pay(CcmsSmartleasingExpenseManag expenseManag, String token) {
        //得到登录人id
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        String sTmp = expenseManag.getFkContractId();
        String contractId = sTmp == null? "":sTmp;
        CcmsSmartleasingContract contractAsCriteria = new CcmsSmartleasingContract();
        contractAsCriteria.setPkContractId(contractId);
        CcmsSmartleasingContract contract = contractMapper.selectOne(contractAsCriteria);

        BigDecimal bdTmp = expenseManag.getPaidInExpenses();
        BigDecimal bdPIE = bdTmp == null? getBigDecimalValue("0"):bdTmp;
        bdTmp = expenseManag.getExpensesReceivable();
        BigDecimal bdER = bdTmp == null? getBigDecimalValue("0"):bdTmp;

        //完善费用信息实体，并修改缴费状态信息
        expenseManag.setLastUpdatedBy(currency.getLoginId());
        expenseManag.setLastUpdateDate(new Date());
        expenseManag.setPaymentTime(new Date());
        expenseManag.setPaymentMethod(OFFLINE_PAY);

        bdTmp = contract.getAmountOfAccountsReceivable();
        BigDecimal bdAoAR = bdTmp == null ? getBigDecimalValue("0"):bdTmp;
        bdTmp = contract.getPrepaymentBalance();
        BigDecimal bdPPB = bdTmp == null ? getBigDecimalValue("0"):bdTmp;
        bdTmp = contract.getTotalBill();
        BigDecimal bdTB = bdTmp == null ? getBigDecimalValue("0") : bdTmp;

        String tenantPayStatus = TM_NEED_PAY;
        int i;
        expenseManag.setPaidInExpenses(bdPIE);
        contract.setAmountOfAccountsReceivable(bdAoAR.add(bdPIE));
        contract.setTotalBill(bdTB.subtract(bdPIE));

        if (bdPIE.compareTo(bdER) == -1) { //less than
            expenseManag.setExpensesReceivable(bdER.subtract(bdPIE));
            expenseManag.setPaymentStatus(EM_NEED_PAY);
        }
        else if (bdPIE.compareTo(bdER) == 0) { //equal to
            expenseManag.setExpensesReceivable(getBigDecimalValue("0"));
            expenseManag.setPaymentStatus(EM_PAID);

            List<CcmsSmartleasingExpenseManag> EMList = expenseManagMapper.getAllByCondition(null,null,EM_NEED_PAY,null,null,null,null,null,null,null,null,null);
            i =0 ;
            for ( CcmsSmartleasingExpenseManag entry : EMList ) {
                if (entry.getStatisticsMonth()!= expenseManag.getStatisticsMonth()){
                    i++;
                    break;
                }
            }
            tenantPayStatus = i>0 ? TM_NEED_PAY : TM_PAID;
        }
        else { //more than
            expenseManag.setExpensesReceivable(bdER.subtract(bdPIE));
            expenseManag.setPaymentStatus(EM_PAID);

            contract.setPrepaymentBalance(bdPPB.add(bdPIE.subtract(bdER)));

            List<CcmsSmartleasingExpenseManag> EMList = expenseManagMapper.getAllByCondition(null,null,EM_NEED_PAY,null,null,null,null,null,null,null,null,null);
            i =0 ;
            for ( CcmsSmartleasingExpenseManag entry : EMList ) {
                if (entry.getStatisticsMonth()!= expenseManag.getStatisticsMonth()){
                    i++;
                    break;
                }
            }
            tenantPayStatus = i>0 ? TM_NEED_PAY : TM_PAID;
        }


        expenseManagMapper.updateById(expenseManag);
        contractMapper.updateById(contract);
        tenantMapper.updateByContractId(new Date(), currency.getLoginId(), tenantPayStatus, expenseManag.getFkContractId());

        CcmsSmartleasingHistoricalPayment hp = new CcmsSmartleasingHistoricalPayment();

        hp.setPkHistoricalPaymentId(String.valueOf(UUID.randomUUID()));
        hp.setCollectionMonth(expenseManag.getStatisticsMonth());
        hp.setCreatedBy(currency.getLoginId());
        hp.setCreationDate(new Date());
        hp.setPaymentMethod(OFFLINE_PAY);
        hp.setAmountCollected(bdPIE);
        hp.setPaymentInstruction(expenseManag.getPaymentInstruction());
        hp.setPaymentVoucher(expenseManag.getPaymentVoucher());
        hp.setFkExpenseManagId(expenseManag.getPkExpenseManagId());
        historicalPaymentMapper.insert(hp);

        //
        // regionalAnalysisMapper.updateByCostId(expenseManag.getPaidInExpenses(), new Date(), 0, expenseManag.getPkExpenseManagId());

        return Result.success();
    }

    /**
     * @Description: 审核的回显信息获取
     * @author zhuliang
     * @date 2020/12/5 10:03
     */
    @Override
    public Result<CcmsSmartleasingNotice> getAuditEcho(String pkExpenseManagId, String token) {
        //得到登录人信息
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //根据费用信息id查询通知单信息
        //CcmsSmartleasingNotice notice = noticeMapper.getNoticeByExpenseManagId(pkExpenseManagId, currency.getProperty(), currency.getManagementOffice());

        CcmsSmartleasingNotice notice = noticeMapper.getNewNoticeByExpenseManagId(pkExpenseManagId, currency.getProperty(), currency.getManagementOffice());

        //根据费用信息id查询本月录入信息和前三月录入的水电煤信息
        CcmsSmartleasingExpenseEntry expenseEntry = expenseEntryMapper.getHydropowerByExpenseManagId(pkExpenseManagId);

        if (null == expenseEntry) {
            expenseEntry = new CcmsSmartleasingExpenseEntry();
        }

        //将水电煤信息写入返回值中
        notice.setExpenseEntry(expenseEntry);

        return Result.success(notice);
    }

    /**
     * @Description: 抄表审核
     * @author zhuliang
     * @date 2020/12/5 11:03
     */
    @Override
    @Transactional
    public Result meterReadingReview(CcmsSmartleasingExpenseEntry expenseEntry, String token) {
        //得到登录人id
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //得到对应的通知单信息和费用列表信息
        CcmsSmartleasingNotice notice = noticeMapper.getAllByExpenseManagId(expenseEntry.getFkRoomId(), expenseEntry.getFkExpenseManagId());
        CcmsSmartleasingExpenseManag expenseManag = expenseManagMapper.selectById(expenseEntry.getFkExpenseManagId());

        //将抄表信息写入数据库
        expenseEntry.setLastUpdatedBy(currency.getLoginId());
        expenseEntry.setLastUpdateDate(new Date());
        if (expenseEntry.getElevatorFee()==null){
            //将抄表信息电梯费置为空
            expenseEntryMapper.updateElevatorFeeToNull(expenseEntry.getPkExpenseEntryId());
        }
        if (expenseEntry.getSanitationFee()==null){
            //将抄表信息卫生费置为空
            expenseEntryMapper.updateSanitationFeeToNull(expenseEntry.getPkExpenseEntryId());
        }
        if (expenseEntry.getMaintenanceFund()==null){
            //将抄表信息本体维修基金置为空
            expenseEntryMapper.updateMaintenanceFundToNull(expenseEntry.getPkExpenseEntryId());
        }
        if (expenseEntry.getGasInsurance()==null){
            //将电视费/煤保置为空
            expenseEntryMapper.updateGasInsuranceToNull(expenseEntry.getPkExpenseEntryId());
        }
        expenseEntryMapper.updateById(expenseEntry);

        //修改通知单数据，并写入数据库
        if (StringUtils.isNotBlank(notice.getWaterCostCode())) {
            //本月行度（水费）
            notice.setMonthWaterMobility(expenseEntry.getMonthWaterMobility());
            //上月行度（水费）
            notice.setLastMonthWaterMobility(expenseEntry.getLastMonthWaterMobility());
            //水用量 = 本月行度 - 上月行度
            BigDecimal monthWaterConsumption = expenseEntry.getMonthWaterConsumption();
            notice.setWaterConsumption(monthWaterConsumption);
            //水损量 = 水用量 * 水损系数
            notice.setWaterLossConsumption(notice.getWaterConsumption().multiply(notice.getWaterLossCoefficient()).setScale(2, RoundingMode.HALF_UP));
            //实际水量方便后面计算
            BigDecimal actualWater = monthWaterConsumption.add(notice.getWaterLossConsumption());
            //计算水费  水费 =（水用量 + 水损量）* 分段水价   计算排水费  排水费 =（水用量 + 水损量）* 排水费系数 * 分段排水费水价
            BigDecimal waterCharges;    //水费
            BigDecimal drainageFee;     //排水费
            BigDecimal lessThanWater = new BigDecimal(notice.getLessThanWater());
            BigDecimal greaterThanWater = new BigDecimal(notice.getGreaterThanWater());
            BigDecimal pressureAdministrationCosts;
            CcmsSmartleasingCoefficientTemplate ccmsSmartleasingCoefficientTemplate = coefficientTemplateMapper.selectGarbageDisposalFee(expenseEntry.getFkExpenseManagId());
            if (ccmsSmartleasingCoefficientTemplate != null) {
                if (actualWater.compareTo(new BigDecimal(0)) == 0) {
                    //水用量为0时
                    waterCharges = new BigDecimal(0);
                    //水用量为0,排水费也为0
                    drainageFee = new BigDecimal(0);
                    //水用量为0,加压管理费也为0
                    pressureAdministrationCosts = new BigDecimal(0);
                } else if (actualWater.compareTo(lessThanWater) == -1) {
                    //小于最低水吨数，直接计算
                    waterCharges = actualWater.multiply(notice.getLessUnitPriceOfWater()).setScale(2, RoundingMode.HALF_UP);
                    //排水费计算
                    drainageFee = actualWater.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getLessUnitPriceDrainageFee()).setScale(2, RoundingMode.HALF_UP);
                    //加压管理费计算 加压管理费 = 水用量 * 加压系数 * 分段水价
                    pressureAdministrationCosts = monthWaterConsumption.multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getLessUnitPriceOfWater());
                } else if (actualWater.compareTo(greaterThanWater) < 1) {
                    //计算水量
                    BigDecimal subtract = actualWater.subtract(lessThanWater);
                    //小于等于最高水吨数
                    waterCharges = lessThanWater.multiply(notice.getLessUnitPriceOfWater());
                    waterCharges = waterCharges.add(subtract.multiply(notice.getMiddleUnitPriceOfWater())).setScale(2, RoundingMode.HALF_UP);
                    //排水费计算
                    drainageFee = lessThanWater.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getLessUnitPriceDrainageFee());
                    drainageFee = drainageFee.add(subtract.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getMiddleUnitPriceDrainageFee())).setScale(2, RoundingMode.HALF_UP);
                    //加压管理费计算 加压管理费 = 水用量 * 加压系数 * 分段水价
                    pressureAdministrationCosts = lessThanWater.multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getLessUnitPriceOfWater());
                    pressureAdministrationCosts = pressureAdministrationCosts.add((monthWaterConsumption.subtract(lessThanWater)).multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getMiddleUnitPriceOfWater()));
                } else {
                    //计算水量
                    BigDecimal middle = greaterThanWater.subtract(lessThanWater);
                    BigDecimal subtract = actualWater.subtract(greaterThanWater);
                    //大于最高水吨数
                    waterCharges = lessThanWater.multiply(notice.getLessUnitPriceOfWater());
                    waterCharges = waterCharges.add(middle.multiply(notice.getMiddleUnitPriceOfWater()));
                    waterCharges = waterCharges.add(subtract.multiply(notice.getGreaterUnitPriceOfWater())).setScale(2, RoundingMode.HALF_UP);
                    //排水费计算
                    drainageFee = lessThanWater.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getLessUnitPriceDrainageFee());
                    drainageFee = drainageFee.add(middle.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getMiddleUnitPriceDrainageFee()));
                    drainageFee = drainageFee.add(subtract.multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP).multiply(notice.getGreaterUnitPriceDrainageFee())).setScale(2, RoundingMode.HALF_UP);
                    //加压管理费计算 加压管理费 = 水用量 * 加压系数 * 分段水价
                    pressureAdministrationCosts = lessThanWater.multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getLessUnitPriceOfWater());
                    pressureAdministrationCosts = pressureAdministrationCosts.add(middle.multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getMiddleUnitPriceOfWater()));
                    pressureAdministrationCosts = pressureAdministrationCosts.add((monthWaterConsumption.subtract(greaterThanWater)).multiply(ccmsSmartleasingCoefficientTemplate.getPressurizationcoefficient()).multiply(ccmsSmartleasingCoefficientTemplate.getGreaterUnitPriceOfWater()));
                }
                //加压管理费 = 水用量 * 加压系数 * 分段水价
                notice.setPressureAdministrationCosts(pressureAdministrationCosts);
                //排水费 = （水用量 + 水损量）* 排水费系数 * 分段排水费水价
                notice.setDrainageFee(drainageFee);
                //水费税金 = （水费 + 加压管理费） * 水费税率
                notice.setWaterTariff((waterCharges.add(notice.getPressureAdministrationCosts())).multiply(ccmsSmartleasingCoefficientTemplate.getWaterTariffRate()).setScale(2, RoundingMode.HALF_UP));
                //基本水费 = 审核录入
                notice.setBasicWaterPrice(expenseEntry.getBasicWaterPrice());
                //水小计 = 水费 + 水费税金 + 加压管理费 + 基本水费
                notice.setWaterRentSubtotal(waterCharges.add(notice.getWaterTariff()).add(notice.getPressureAdministrationCosts()).add(notice.getBasicWaterPrice()));
            }
        }

        //hot water
        if (StringUtils.isNotBlank(notice.getHotWaterCostCode())) {
            notice.setMonthHotWaterMobility(expenseEntry.getMonthHotWaterMobility());
            notice.setLastMonthHotWaterMobility(expenseEntry.getLastMonthHotWaterMobility());
            BigDecimal monthHotWaterConsumption = expenseEntry.getMonthHotWaterConsumption();
            notice.setHotWaterConsumption(monthHotWaterConsumption);
            notice.setHotWaterRentSubtotal(monthHotWaterConsumption.multiply(notice.getUnitPriceHotWater()));
        }

        if (StringUtils.isNotBlank(notice.getElectricCostCode())) {
            //本月行度（电费）
            notice.setMonthElectricityMobility(expenseEntry.getMonthElectricityMobility());
            //上月行度（电费）
            notice.setLastMonthElectricityMobility(expenseEntry.getLastMonthElectricityMobility());
            //公共用电分摊 =（本月行度-上月行度）* 电损系数
            notice.setPublicElectricitySharing(expenseEntry.getMonthElectricityConsumption().multiply(notice.getElectricLossCoefficient()).setScale(2, RoundingMode.HALF_UP));
            //实际用量 = （本月行度-上月行度）+ 公共用电分摊
            BigDecimal electricActualConsumption = expenseEntry.getMonthElectricityConsumption().add(notice.getPublicElectricitySharing());
            notice.setElectricActualConsumption(electricActualConsumption);
            //电费 = （实际用量）* 分段电价
            BigDecimal electricityFees;
            BigDecimal lessElectricityConsumption = new BigDecimal(notice.getLessElectricityConsumption());
            BigDecimal greaterElectricityConsumption = new BigDecimal(notice.getGreaterElectricityConsumption());
            if (electricActualConsumption.compareTo(new BigDecimal(0)) == 0) {
                //电用量为0时
                electricityFees = new BigDecimal(0);
            } else if (electricActualConsumption.compareTo(lessElectricityConsumption) == -1) {
                //小于最低电用量，直接计算
                electricityFees = electricActualConsumption.multiply(notice.getLessUnitPriceOfElectricity()).setScale(2, RoundingMode.HALF_UP);
            } else if (electricActualConsumption.compareTo(greaterElectricityConsumption) < 1) {
                //小于等于最高电用量
                electricityFees = lessElectricityConsumption.multiply(notice.getLessUnitPriceOfElectricity());
                electricityFees = electricityFees.add((electricActualConsumption.subtract(lessElectricityConsumption)).multiply(notice.getMiddleUnitPriceOfElectricity())).setScale(2, RoundingMode.HALF_UP);
            } else {
                //大于最高电用量
                electricityFees = lessElectricityConsumption.multiply(notice.getLessUnitPriceOfElectricity());
                electricityFees = electricityFees.add((greaterElectricityConsumption.subtract(lessElectricityConsumption)).multiply(notice.getMiddleUnitPriceOfElectricity()));
                electricityFees = electricityFees.add((electricActualConsumption.subtract(greaterElectricityConsumption)).multiply(notice.getGreaterUnitPriceOfElectricity())).setScale(2, RoundingMode.HALF_UP);
            }
            //基本电价 = 抄表录入
            notice.setBasicElectricityPrice(expenseEntry.getBasicElectricityPrice());
            //电小计 = 电费 + 基本电价
            notice.setElectricRentSubtotal(electricityFees.add(notice.getBasicElectricityPrice()));
        }
        if (StringUtils.isNotBlank(notice.getCoalGasCostCode())) {
            //本月行度（煤气）
            notice.setMonthGasMobility(expenseEntry.getMonthGasMobility());
            //上月行度（煤气）
            notice.setLastGasMobility(expenseEntry.getLastMonthGasMobility());
            //本月用气量=本月表数-上月表数
            notice.setMonthGasConsumption(expenseEntry.getMonthGasConsumption());
            //欠费
            notice.setGasArrears(expenseEntry.getGasArrears());
            //换表费
            notice.setGasChangeWatchSpense(expenseEntry.getGasChangeWatchSpense());
            //气费=抄表录入
            notice.setGasShortage(expenseEntry.getGasShortage());
            //小计=气费+换表费+欠费
            notice.setGasRentSubtotal(notice.getGasShortage().add(notice.getGasArrears()).add(notice.getGasChangeWatchSpense()));
        }
        //修改房租
        notice.setMonthlyRent(expenseEntry.getMonthlyRent());
        //其他 = 抄表录入
        notice.setOtherPrice(expenseEntry.getOtherPrice());
        //电梯费 = 抄表录入
        if (expenseEntry.getElevatorFee() != null){
            notice.setElevatorRate(expenseEntry.getElevatorFee());
        } else {
            noticeMapper.updateElevatorFeeToNull(notice.getPkNoticeId());
        }
        //卫生费 = 抄表录入
        if (expenseEntry.getSanitationFee() != null){
            notice.setSanitationFee(expenseEntry.getSanitationFee());
        } else {
            noticeMapper.updateSanitationFeeToNull(notice.getPkNoticeId());
        }
        //本体维修基金 = 抄表录入
        if (expenseEntry.getMaintenanceFund() != null){
            notice.setMaintenanceFund(expenseEntry.getMaintenanceFund());
        } else {
            noticeMapper.updateMaintenanceFundToNull(notice.getPkNoticeId());
        }
        //电视费/煤保 = 抄表录入
        if (expenseEntry.getGasInsurance() != null){
            notice.setGasInsurance(expenseEntry.getGasInsurance());
        } else {
            noticeMapper.updateGasInsuranceToNull(notice.getPkNoticeId());
        }
        //综合管理费 = 抄表录入
        notice.setComprehensiveManagementRate(expenseEntry.getComprehensiveManagementRate());
        //根据费用ID去模板里获取垃圾处理费
        CcmsSmartleasingCoefficientTemplate ccmsSmartleasingCoefficientTemplate = coefficientTemplateMapper.selectGarbageDisposalFee(expenseEntry.getFkExpenseManagId());
        BigDecimal garbageDisposalFee = ccmsSmartleasingCoefficientTemplate.getGarbageDisposalFee();
        if (garbageDisposalFee != null && garbageDisposalFee.compareTo(new BigDecimal(0)) > 0) {
            notice.setTrashHandlingFee(garbageDisposalFee);
        } else {
            //垃圾处理费=取整【（用水量（水费本月行度-水费上月行度）+水损量）*垃圾处理费倍率*排水费系数】*垃圾处理费单价
            if (null != notice.getWaterConsumption() && !notice.getWaterConsumption().equals(new BigDecimal(0))) {
                //计算用量
                BigDecimal consumption = notice.getWaterConsumption().add(notice.getWaterLossConsumption());
                BigDecimal bigDecimal = consumption.multiply(notice.getGarbageTreatmentMagnification()).multiply(notice.getDrainageCoefficient()).setScale(0, RoundingMode.HALF_UP);
                ;
                notice.setTrashHandlingFee(bigDecimal.multiply(notice.getUnitPriceGarbageTreatment()));
            } else {
                notice.setTrashHandlingFee(new BigDecimal(0));
            }
        }
        //其他费用小计 = 房租 + 综合管理费 + 排水费 + 垃圾处理费 + 电话费 + 电梯费 + 卫生费 + 本体维修基金 + 电视费/煤保 + 其他
        notice.setOtherSubtotal(notice.getMonthlyRent().add(notice.getTrashHandlingFee()).add(notice.getOtherPrice()).add(notice.getComprehensiveManagementRate()));
        if (null != notice.getDrainageFee()) {
            // 排水费
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getDrainageFee()));
        }
        if (null != notice.getTelephoneRate()) {
            // 电话费
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getTelephoneRate()));
        }
        if (null != notice.getSanitationFee()) {
            // 卫生费
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getSanitationFee()));
        }
        if (null != notice.getElevatorRate()) {
            // 电梯费
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getElevatorRate()));
        }
        if (null != notice.getGasInsurance()) {
            // 电视费/煤保
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getGasInsurance()));
        }
        if (null != notice.getMaintenanceFund()){
            // 本体维修基金
            notice.setOtherSubtotal(notice.getOtherSubtotal().add(notice.getMaintenanceFund()));
        }

        //本月应收总额=水费小计+电费小计+煤气费小计+其他费用小计
        notice.setTotalAmount(notice.getOtherSubtotal());
        if (null != notice.getWaterRentSubtotal()) {
            notice.setTotalAmount(notice.getTotalAmount().add(notice.getWaterRentSubtotal()));
        }
        if (null != notice.getElectricRentSubtotal()) {
            notice.setTotalAmount(notice.getTotalAmount().add(notice.getElectricRentSubtotal()));
        }
        if (null != notice.getGasRentSubtotal()) {
            notice.setTotalAmount(notice.getTotalAmount().add(notice.getGasRentSubtotal()));
        }

        //应收费用
        notice.setExpensesReceivable(notice.getTotalAmount());

        //修改信息
        notice.setLastUpdateDate(new Date());
        notice.setLastUpdatedBy(currency.getLoginId());
        noticeMapper.updateById(notice);

        //修改费用列表信息数据，并写入数据库
        expenseManag.setLastUpdatedBy(currency.getLoginId());
        expenseManag.setLastUpdateDate(new Date());
        expenseManag.setInvoiceStatus("0");
        expenseManag.setMonthlyInvoiceStatus("0");
        expenseManag.setPaymentStatus("0");
        expenseManag.setTotalAmount(notice.getTotalAmount());
        expenseManag.setExpensesReceivable(notice.getTotalAmount());
        expenseManagMapper.updateById(expenseManag);

        return Result.success();
    }

    /**
     * @Description: 线下开票
     * @author zhuliang
     * @date 2020/12/5 17:55
     */
    @Override
    @Transactional
    public Result offlineInvoicing(CcmsSmartleasingExpenseManag expenseManag, String token) {
        //得到登录人id
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //兼容性处理，线上模式不能开取线下发票
        if (StringUtils.isBlank(expenseManag.getInvoiceMethod()) || !"1".equals(expenseManag.getInvoiceMethod())) {
            return Result.error(CodeMsg.OFFLINE_INVOICEC_CANNOT_IN_ONLINE_MODE);
        }

        //完善信息
        expenseManag.setLastUpdateDate(new Date());
        expenseManag.setLastUpdatedBy(currency.getLoginId());
        expenseManag.setInvoiceStatus("1");
        expenseManag.setMonthlyInvoiceStatus("1");

        //修改信息
        expenseManagMapper.updateById(expenseManag);

        //定义要修改的通知单信息
        CcmsSmartleasingNotice notice = new CcmsSmartleasingNotice();
        notice.setLastUpdatedBy(currency.getLoginId());
        notice.setLastUpdateDate(new Date());

        //修改通知单信息
        noticeMapper.update(notice, new EntityWrapper<CcmsSmartleasingNotice>().eq("fk_expense_manag_id", expenseManag.getPkExpenseManagId()));

        return Result.success();
    }

    /**
     * @Description: 通过费用信息id的字符串集合来进行批量开票
     * @author zhuliang
     * @date 2020/11/30 9:45
     */
    @Override
    @Transactional
    public Result invoicing(String pkExpenseManagId, String token) {
        //得到登录人id
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //根据id查询要开票所需要的信息
        CcmsSmartleasingNotice notice = noticeMapper.getBillingInformation(pkExpenseManagId);

        //兼容性处理，防止还未抄表审核
        if (StringUtils.isBlank(notice.getInvoiceStatus())) {
            return Result.error(CodeMsg.COST_SHEET_NOT_METER_READING_OR_REVIEW);
        }

        //兼容性处理，防止重复开票
        if (!"0".equals(notice.getInvoiceStatus())) {
            return Result.error(CodeMsg.CANNOT_DOUBLE_INVOICE);
        }

        //定义需要的map集合对象
        HashMap<String, Object> billingMap = new HashMap();                     //开票map，用来存放所有开票数据
        HashMap<String, Object> requestBodyMap = new HashMap<>();               //请求体map，用来存放请求体信息
        List<HashMap> invoiceDetails = new ArrayList<>();                       //发票明细集合

        //定义日期格式
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy年MM月");
        SimpleDateFormat timeDateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //生成订单号
        String orderNumber = timeDateFormat.format(new Date());
        String random = new Random().nextInt(900) + 100 + "";
        orderNumber = orderNumber + random;

        //写入明细信息，并存入明细集合中
        if (StringUtils.isNotBlank(notice.getWaterCostCode()) && null != notice.getWaterRentSubtotal() && !notice.getWaterRentSubtotal().equals(new BigDecimal("0.00")) && null != notice.getWaterTaxRate() && !notice.getWaterTaxRate().equals(new BigDecimal("0.00"))) {
            //计算费用
            BigDecimal decimal = notice.getWaterRentSubtotal().multiply(notice.getWaterTaxRate()).divide((notice.getWaterTaxRate().add(new BigDecimal(1))), 2, RoundingMode.HALF_UP);

            HashMap<String, String> waterDetailsMap = new HashMap<>();              //水费明细信息map
            waterDetailsMap.put("goodsname", "水费");
            waterDetailsMap.put("taxamt", notice.getWaterRentSubtotal().toString());
            waterDetailsMap.put("tax", decimal.toString());
            waterDetailsMap.put("taxfreeamt", notice.getWaterRentSubtotal().subtract(decimal).toString());
            waterDetailsMap.put("taxrate", notice.getWaterTaxRate().toString());
            waterDetailsMap.put("spec", "");
            waterDetailsMap.put("unit", "立方米");
            waterDetailsMap.put("spbm", notice.getWaterClassificationCode());
            waterDetailsMap.put("hsbz", "0");
            waterDetailsMap.put("fphxz", "0");
            waterDetailsMap.put("yhzcbs", "0");
            waterDetailsMap.put("zzstsgl", "");
            waterDetailsMap.put("lslbs", "");
            waterDetailsMap.put("kce", "");

            invoiceDetails.add(waterDetailsMap);
        }
        if (StringUtils.isNotBlank(notice.getElectricCostCode()) && null != notice.getElectricRentSubtotal() && !notice.getElectricRentSubtotal().equals(new BigDecimal("0.00")) && null != notice.getElectricTaxRate() && !notice.getElectricTaxRate().equals(new BigDecimal("0.00"))) {
            //计算费用
            BigDecimal decimal = notice.getElectricRentSubtotal().multiply(notice.getElectricTaxRate()).divide((notice.getElectricTaxRate().add(new BigDecimal(1))), 2, RoundingMode.HALF_UP);

            HashMap<String, String> electricDetailsMap = new HashMap<>();           //电费明细信息map
            electricDetailsMap.put("goodsname", "电费");
            electricDetailsMap.put("taxamt", notice.getElectricRentSubtotal().toString());
            electricDetailsMap.put("tax", decimal.toString());
            electricDetailsMap.put("taxfreeamt", notice.getElectricRentSubtotal().subtract(decimal).toString());
            electricDetailsMap.put("taxrate", notice.getElectricTaxRate().toString());
            electricDetailsMap.put("spec", "KAV");
            electricDetailsMap.put("unit", "度");
            electricDetailsMap.put("spbm", notice.getElectricClassificationCode());
            electricDetailsMap.put("hsbz", "0");
            electricDetailsMap.put("fphxz", "0");
            electricDetailsMap.put("yhzcbs", "0");
            electricDetailsMap.put("zzstsgl", "");
            electricDetailsMap.put("lslbs", "");
            electricDetailsMap.put("kce", "");
            invoiceDetails.add(electricDetailsMap);
        }
        if (null != notice.getMonthlyRent() && !notice.getMonthlyRent().equals(new BigDecimal("0.00")) && null != notice.getRentTaxRate() && !notice.getRentTaxRate().equals(new BigDecimal("0.00"))) {
            if (notice.getRentTaxRate().compareTo(new BigDecimal("0.015")) == 0) {
                //计算费用
                BigDecimal decimal2 = notice.getMonthlyRent().multiply(notice.getRentTaxRate()).divide(new BigDecimal("1.05"), 2, RoundingMode.HALF_UP);

                //生成订单号
                String orderNumber2 = timeDateFormat.format(new Date());
                String random2 = new Random().nextInt(900) + 100 + "";
                orderNumber2 = orderNumber2 + random2;

                //定义存放的开票信息对象
                CcmsSmartleasingInvoiceRecord record = new CcmsSmartleasingInvoiceRecord();
                record.setCreatedBy(currency.getLoginId())
                        .setCreationDate(new Date())
                        .setLastUpdatedBy(currency.getLoginId())
                        .setLastUpdateDate(new Date())
                        .setOrderNumber(orderNumber2)
                        .setInvoiceType(0)
                        .setPkInvoiceRecordId(UUID.randomUUID() + "")
                        .setFkNoticeId(notice.getPkNoticeId());
                //添加开票信息
                invoiceRecordMapper.insert(record);

                //开发票
                monthlyInvoicing(notice, decimal2, orderNumber2, format, dateFormat, currency);
            } else {
                //计算费用
                BigDecimal decimal = notice.getMonthlyRent().multiply(notice.getRentTaxRate()).divide((notice.getRentTaxRate().add(new BigDecimal(1))), 2, RoundingMode.HALF_UP);
                HashMap<String, String> rentDetailsMap = new HashMap<>();               //房租明细信息map
                rentDetailsMap.put("goodsname", "房租");
                rentDetailsMap.put("taxamt", notice.getMonthlyRent().toString());
                rentDetailsMap.put("tax", decimal.toString());
                rentDetailsMap.put("taxfreeamt", notice.getMonthlyRent().subtract(decimal).toString());
                rentDetailsMap.put("taxrate", notice.getRentTaxRate().toString());
                rentDetailsMap.put("spec", "");
                rentDetailsMap.put("unit", "");
                rentDetailsMap.put("spbm", notice.getRentClassificationCode());
                rentDetailsMap.put("hsbz", "0");
                rentDetailsMap.put("fphxz", "0");
                rentDetailsMap.put("yhzcbs", "0");
                rentDetailsMap.put("zzstsgl", "");
                rentDetailsMap.put("lslbs", "");
                rentDetailsMap.put("kce", "");
                invoiceDetails.add(rentDetailsMap);
            }
        }
        if (null != notice.getComprehensiveManagementRate() && !notice.getComprehensiveManagementRate().equals(new BigDecimal("0.00")) && null != notice.getManagementExpenseTaxRate() && !notice.getManagementExpenseTaxRate().equals(new BigDecimal("0.00"))) {
            //计算费用
            BigDecimal decimal = notice.getComprehensiveManagementRate().multiply(notice.getManagementExpenseTaxRate()).divide((notice.getManagementExpenseTaxRate().add(new BigDecimal(1))), 2, RoundingMode.HALF_UP);

            HashMap<String, String> managementExpenseDetailsMap = new HashMap<>();  //管理费明细信息map
            managementExpenseDetailsMap.put("goodsname", "管理费");
            managementExpenseDetailsMap.put("taxamt", notice.getComprehensiveManagementRate().toString());
            managementExpenseDetailsMap.put("tax", decimal.toString());
            managementExpenseDetailsMap.put("taxfreeamt", notice.getComprehensiveManagementRate().subtract(decimal).toString());
            managementExpenseDetailsMap.put("taxrate", notice.getManagementExpenseTaxRate().toString());
            managementExpenseDetailsMap.put("spec", "");
            managementExpenseDetailsMap.put("unit", "");
            managementExpenseDetailsMap.put("spbm", notice.getManagementExpenseClassificationCode());
            managementExpenseDetailsMap.put("hsbz", "0");
            managementExpenseDetailsMap.put("fphxz", "0");
            managementExpenseDetailsMap.put("yhzcbs", "0");
            managementExpenseDetailsMap.put("zzstsgl", "");
            managementExpenseDetailsMap.put("lslbs", "");
            managementExpenseDetailsMap.put("kce", "");
            invoiceDetails.add(managementExpenseDetailsMap);
        }
        if (null != notice.getSanitationFee() && !notice.getSanitationFee().equals(new BigDecimal("0.00")) && null != notice.getSanitaryFeeTaxRate() && !notice.getSanitaryFeeTaxRate().equals(new BigDecimal("0.00"))) {
            //计算费用
            BigDecimal decimal = notice.getSanitationFee().multiply(notice.getSanitaryFeeTaxRate()).divide((notice.getSanitaryFeeTaxRate().add(new BigDecimal(1))), 2, RoundingMode.HALF_UP);

            HashMap<String, String> sanitaryDetailsMap = new HashMap<>();           //卫生清洁费明细信息map
            sanitaryDetailsMap.put("goodsname", "卫生清洁费");
            sanitaryDetailsMap.put("taxamt", notice.getSanitationFee().toString());
            sanitaryDetailsMap.put("tax", decimal.toString());
            sanitaryDetailsMap.put("taxfreeamt", notice.getSanitationFee().subtract(decimal).toString());
            sanitaryDetailsMap.put("taxrate", notice.getSanitaryFeeTaxRate().toString());
            sanitaryDetailsMap.put("spec", "");
            sanitaryDetailsMap.put("unit", "");
            sanitaryDetailsMap.put("spbm", notice.getSanitaryFeeClassificationCode());
            sanitaryDetailsMap.put("hsbz", "0");
            sanitaryDetailsMap.put("fphxz", "0");
            sanitaryDetailsMap.put("yhzcbs", "0");
            sanitaryDetailsMap.put("zzstsgl", "");
            sanitaryDetailsMap.put("lslbs", "");
            sanitaryDetailsMap.put("kce", "");
            invoiceDetails.add(sanitaryDetailsMap);
        }
        if (null != notice.getElevatorRate() && !notice.getElevatorRate().equals(new BigDecimal("0.00")) && null != notice.getElevatorFeeTaxRate() && !notice.getElevatorFeeTaxRate().equals(new BigDecimal("0.00"))) {
            //计算费用
            BigDecimal decimal = notice.getElevatorRate().multiply(notice.getElevatorFeeTaxRate()).divide((notice.getElevatorFeeTaxRate().add(new BigDecimal(1))), 2, RoundingMode.HALF_UP);

            HashMap<String, String> elevatorDetailsMap = new HashMap<>();           //电梯费明细信息map
            elevatorDetailsMap.put("goodsname", "电梯使用费");
            elevatorDetailsMap.put("taxamt", notice.getElevatorRate().toString());
            elevatorDetailsMap.put("tax", decimal.toString());
            elevatorDetailsMap.put("taxfreeamt", notice.getElevatorRate().subtract(decimal).toString());
            elevatorDetailsMap.put("taxrate", notice.getElevatorFeeTaxRate().toString());
            elevatorDetailsMap.put("spec", "");
            elevatorDetailsMap.put("unit", "");
            elevatorDetailsMap.put("spbm", notice.getElevatorFeeClassificationCode());
            elevatorDetailsMap.put("hsbz", "0");
            elevatorDetailsMap.put("fphxz", "0");
            elevatorDetailsMap.put("yhzcbs", "0");
            elevatorDetailsMap.put("zzstsgl", "");
            elevatorDetailsMap.put("lslbs", "");
            elevatorDetailsMap.put("kce", "");
            invoiceDetails.add(elevatorDetailsMap);
        }

        //存放请求体信息
        requestBodyMap.put("buyername", notice.getTenantName());//购方名称，必须，企业名称或个人姓名
        if ("1".equals(notice.getTenantType())) {
            requestBodyMap.put("taxnum", notice.getIdcardNumber());//购方企业税号，个人为空
        }
        requestBodyMap.put("orderno", orderNumber);//必须，订单号，且每单唯一
        requestBodyMap.put("invoicedate", format.format(new Date()));//必须，单据时间
        requestBodyMap.put("saletaxnum", basicConfiguration.getSaletaxnum());//必须，销方企业税号
        requestBodyMap.put("saleaccount", basicConfiguration.getSaleaccount());//销售方企业银行开户行及账号
        requestBodyMap.put("salephone", basicConfiguration.getSalephone());//销售方电话
        requestBodyMap.put("saleaddress", basicConfiguration.getSaleaddress());//销方企业地址
        requestBodyMap.put("kptype", "1");//必须，发票类型：1:正票;2：红票
        requestBodyMap.put("message", notice.getCity() + notice.getArea() + notice.getStreet() + notice.getAddress() + "  " + dateFormat.format(notice.getExpenseMonth()));//备注——租户地址和租金年月
        requestBodyMap.put("clerk", basicConfiguration.getClerk());//必须，开票人
        requestBodyMap.put("payee", basicConfiguration.getPayee());//收款人
        requestBodyMap.put("checker", basicConfiguration.getChecker());//复核人
        requestBodyMap.put("tsfs", "0");//推送方式， -1:不推送;0:邮箱;1:手机(默认);2:邮箱&手机
        requestBodyMap.put("email", notice.getEmail());//推送邮箱
        requestBodyMap.put("phone", notice.getContactPhone());//必须，推送手机(开票成功会短信提醒购方)
        requestBodyMap.put("invoiceLine", "p");//默认为普通电票 p。
        requestBodyMap.put("detail", invoiceDetails);//发票明细

        //放入身份认证号
        billingMap.put("identity", basicConfiguration.getIdentity());
        billingMap.put("order", requestBodyMap);

        //加密
        String order = DESDZFP.encrypt(new JSONObject(billingMap) + "");

        HashMap<String, String> map = new HashMap<>();
        map.put("orderNumber", orderNumber);
        map.put("order", order);
        map.put("url", "/nnfpbox");
//        map.put("url", ConstantConfiguration.invoiceOfficialRequestAddress);

        //定义存放的开票信息对象
        CcmsSmartleasingInvoiceRecord invoiceRecord = new CcmsSmartleasingInvoiceRecord();
        invoiceRecord.setCreatedBy(currency.getLoginId())
                .setCreationDate(new Date())
                .setLastUpdatedBy(currency.getLoginId())
                .setLastUpdateDate(new Date())
                .setOrderNumber(orderNumber)
                .setFkNoticeId(notice.getPkNoticeId())
                .setPkInvoiceRecordId(UUID.randomUUID() + "");
        if (notice.getRentTaxRate().compareTo(new BigDecimal("0.015")) == 0 && null != notice.getMonthlyRent() && !notice.getMonthlyRent().equals(new BigDecimal("0.00")) && null != notice.getRentTaxRate() && !notice.getRentTaxRate().equals(new BigDecimal("0.00"))) {
            invoiceRecord.setInvoiceType(1);
        } else {
            invoiceRecord.setInvoiceType(2);    //企业发票统一
        }
        //添加开票信息
        invoiceRecordMapper.insert(invoiceRecord);

        //修改通知单的开票信息
        notice.setLastUpdateDate(new Date());
        notice.setLastUpdatedBy(currency.getLoginId());
        noticeMapper.updateById(notice);

        return Result.success(map);
    }

    private void monthlyInvoicing(CcmsSmartleasingNotice notice, BigDecimal decimal, String orderNumber2, SimpleDateFormat format, SimpleDateFormat dateFormat, Currency currency) {
        //定义需要的map集合对象
        HashMap<String, Object> billingMap = new HashMap();                     //开票map，用来存放所有开票数据
        HashMap<String, Object> requestBodyMap = new HashMap<>();               //请求体map，用来存放请求体信息
        List<HashMap> invoiceDetails = new ArrayList<>();                       //发票明细集合
        HashMap<String, String> rentDetailsMap = new HashMap<>();               //房租明细信息map
        rentDetailsMap.put("goodsname", "房租");
        rentDetailsMap.put("taxamt", notice.getMonthlyRent().toString());
        rentDetailsMap.put("tax", decimal.toString());
        rentDetailsMap.put("taxfreeamt", notice.getMonthlyRent().subtract(decimal).toString());
        rentDetailsMap.put("taxrate", "0.015");
        rentDetailsMap.put("spec", "");
        rentDetailsMap.put("unit", "");
        rentDetailsMap.put("spbm", notice.getRentClassificationCode());
        rentDetailsMap.put("hsbz", "1");
        rentDetailsMap.put("fphxz", "0");
        rentDetailsMap.put("lslbs", "");
        rentDetailsMap.put("kce", "");
        rentDetailsMap.put("yhzcbs", "1");
        rentDetailsMap.put("zzstsgl", "按5%简易征收减按1.5%计征");
        invoiceDetails.add(rentDetailsMap);
        //存放请求体信息
        requestBodyMap.put("buyername", notice.getTenantName());//购方名称，必须，企业名称或个人姓名
        if ("1".equals(notice.getTenantType())) {
            requestBodyMap.put("taxnum", notice.getIdcardNumber());//购方企业税号，个人为空
        }
        requestBodyMap.put("orderno", orderNumber2);//必须，订单号，且每单唯一
        requestBodyMap.put("invoicedate", format.format(new Date()));//必须，单据时间
        requestBodyMap.put("saletaxnum", basicConfiguration.getSaletaxnum());//必须，销方企业税号
        requestBodyMap.put("saleaccount", basicConfiguration.getSaleaccount());//销售方企业银行开户行及账号
        requestBodyMap.put("salephone", basicConfiguration.getSalephone());//销售方电话
        requestBodyMap.put("saleaddress", basicConfiguration.getSaleaddress());//销方企业地址
        requestBodyMap.put("kptype", "1");//必须，发票类型：1:正票;2：红票
        requestBodyMap.put("message", notice.getCity() + notice.getArea() + notice.getStreet() + notice.getAddress() + "  " + dateFormat.format(notice.getExpenseMonth()));//备注——租户地址和租金年月
        requestBodyMap.put("clerk", basicConfiguration.getClerk());//必须，开票人
        requestBodyMap.put("payee", basicConfiguration.getPayee());//收款人
        requestBodyMap.put("checker", basicConfiguration.getChecker());//复核人
        requestBodyMap.put("tsfs", "0");//推送方式， -1:不推送;0:邮箱;1:手机(默认);2:邮箱&手机
        requestBodyMap.put("email", notice.getEmail());//推送邮箱
        requestBodyMap.put("phone", notice.getContactPhone());//必须，推送手机(开票成功会短信提醒购方)
        requestBodyMap.put("invoiceLine", "p");//默认为普通电票 p。
        requestBodyMap.put("detail", invoiceDetails);//发票明细

        //放入身份认证号
        billingMap.put("identity", basicConfiguration.getIdentity());
        billingMap.put("order", requestBodyMap);

        //发生开发票请求
        String request = HttpUtil.request(basicConfiguration.getNginxUrlPC() + "/nnfpbox", JSONObject.toJSONString(billingMap));

        //返回值解析
        //将返回值转换为map集合
        HashMap requestMap = JSONObject.parseObject(request, HashMap.class);
        //根据订单号查询开票信息
        CcmsSmartleasingInvoiceRecord invoice = invoiceRecordMapper.getInvoiceByOrderNumber(orderNumber2);
        invoice.setLastUpdatedBy(currency.getLoginId())
                .setLastUpdateDate(new Date())
                .setStatus(requestMap.get("status") + "")
                .setMessage(requestMap.get("message") + "");

        //判断是否请求通过
        if ("0000".equals(requestMap.get("status"))) {

            invoice.setFpqqlsh(requestMap.get("fpqqlsh").toString());
            //添加开票信息
            invoiceRecordMapper.updateById(invoice);

            //发送延时消息
            Message message = new Message();
            message.setId(invoice.getPkInvoiceRecordId());
            message.setBody(requestMap.get("fpqqlsh").toString());
            messageSend.sendMessage(message);
        } else {
            //添加开票信息
            invoiceRecordMapper.updateById(invoice);

            //发票服务器错误信息
            log.error("发票服务器错误:" + requestMap.get("message") + "   合同编号：" + notice.getContractNumber());
        }

        //修改费用信息的房租发票开票状态信息
        expenseManagMapper.updateByMonthlyInvoice(new Date(), currency.getLoginId(), "2", notice.getFkExpenseManagId());
    }

    /**
     * @Description: 得到发票接口返回值
     * @author zhuliang
     * @date 2021/2/1 15:28
     */
    @Override
    @Transactional
    public Result getInvoicing(String result, String token, String orderNumber) {
        //得到登录人id
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }

        //将返回值转换为map集合
        HashMap requestMap = JSONObject.parseObject(result, HashMap.class);

        //根据订单号查询开票信息
        CcmsSmartleasingInvoiceRecord invoice = invoiceRecordMapper.getInvoiceByOrderNumber(orderNumber);
        invoice.setLastUpdatedBy(currency.getLoginId())
                .setLastUpdateDate(new Date())
                .setStatus(requestMap.get("status") + "")
                .setMessage(requestMap.get("message") + "");

        //根据开票信息id查询费用信息
//        CcmsSmartleasingNotice notice = noticeMapper.getNoticeByInvoiceRecordId(invoice.getFkNoticeId());
        CcmsSmartleasingNotice notice = noticeMapper.selectById(invoice.getFkNoticeId());

        //修改费用信息的开票状态信息
        expenseManagMapper.updateByInvoice(new Date(), currency.getLoginId(), "2", notice.getFkExpenseManagId());

        //判断是否请求通过
        if ("0000".equals(requestMap.get("status"))) {

            invoice.setFpqqlsh(requestMap.get("fpqqlsh").toString());
            //添加开票信息
            invoiceRecordMapper.updateById(invoice);

            //修改通知单状态信息
            notice.setLastUpdateDate(new Date());
            notice.setLastUpdatedBy(currency.getLoginId());

            //修改通知单的开票状态信息
            noticeMapper.updateById(notice);

            //发送延时消息
            Message message = new Message();
            message.setId(invoice.getPkInvoiceRecordId());
            message.setBody(requestMap.get("fpqqlsh").toString());
            messageSend.sendMessage(message);

            return Result.success();
        } else {
            //添加开票信息
            invoiceRecordMapper.updateById(invoice);

            //发票服务器错误信息
            log.error("发票服务器错误:" + requestMap.get("message") + "   合同编号：" + notice.getContractNumber());
            return Result.error(new CodeMsg(20000, requestMap.get("message") + "   合同编号：" + notice.getContractNumber()));
        }


    }

    @Override
    public Result importMeterReadingInformations(MultipartFile file, String token) {
        try {
            List<CcmsSmartleasingExpenseEntry> entries = getEntrys(file);
            ExecutorService es = Executors.newFixedThreadPool(10);
            for(CcmsSmartleasingExpenseEntry entry : entries){
                es.execute(new Runnable() {
                    @Override
                    public void run() {
                        addMeterReadingInformation(entry, token);
                    }
                });
            }
            es.shutdown();
            while (!es.isTerminated()) {
                Thread.sleep(100);
            }
            return Result.success();
        } catch (Exception e) {
            log.error("importMeterReadingInformations error",e);
            return Result.error(new CodeMsg(20000, e.getMessage()));
        }
    }

    @Override
    public Result lockCost(String token) {
        //得到登录人id
        Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
        if (null == currency) {
            currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
        }
        String property = currency.getProperty();
        if(StringUtils.isBlank(property)){
            property = null;
        }
        String managementOffice = currency.getManagementOffice();
        if(StringUtils.isBlank(managementOffice)){
            managementOffice = null;
        }
        // 默认上月
        String oldMonth = DateUtil.format(DateUtil.lastMonth(),"yyyyMM");
        //查询扎帐年月
        String queryMonth = expenseLockMapper.selectMaxMonth(managementOffice,property);

        String currentMonth = DateUtil.format(new Date(),"yyyyMM");
        if(currentMonth.equals(queryMonth)){
            return Result.error(new CodeMsg(20000,DateUtil.parse(currentMonth,"yyyyMM").toString("yyyy-MM")+"月度已扎帐，无需再次扎帐"));
        }
        if(StringUtils.isNotBlank(queryMonth)){
            oldMonth = queryMonth;
        }

        String expenseMonth = DateUtil.format(DateUtil.offsetMonth(DateUtil.parse(oldMonth,"yyyyMM"),1),"yyyyMM");

        // 检查是否已扎帐
        CcmsSmartleasingExpenseLock queryObj = new CcmsSmartleasingExpenseLock();
        queryObj.setLockMonth(expenseMonth);
        queryObj.setFkAdministrativeOfficeId(managementOffice);
        queryObj.setFkPropertyCompanyId(property);
        CcmsSmartleasingExpenseLock retObj = expenseLockMapper.selectOne(queryObj);
        if(retObj != null && retObj.getPkLockId() != null){
            return Result.error(new CodeMsg(20000,DateUtil.parse(expenseMonth,"yyyyMM").toString("yyyy-MM")+"月度已扎帐，无需再次扎帐"));
        }
        // 检查是否存在月度费用
        long count = this.expenseManagMapper.getAllByPage_COUNT(property,managementOffice);
        if(count == 0){
            return Result.error(new CodeMsg(20000,"截至目前,"+DateUtil.parse(queryMonth,"yyyyMM").toString("yyyy-MM")+"月度已完成扎帐，" +DateUtil.parse(expenseMonth,"yyyyMM").toString("yyyy-MM")+"月度无费用数据，不能扎帐"));
        }
        // 检查是否全部抄表审核
        long noAuditCount = this.expenseManagMapper.getNoAuditCount(expenseMonth,property,managementOffice);
        if(noAuditCount > 0){
            return Result.error(new CodeMsg(20000,"截至目前,"+DateUtil.parse(queryMonth,"yyyyMM").toString("yyyy-MM")+"月度已完成扎帐，" +DateUtil.parse(expenseMonth,"yyyyMM").toString("yyyy-MM")+"月度费用未完成审核，不能扎帐"));
        }
        // 执行扎帐
        CcmsSmartleasingExpenseLock expenseLock =  new CcmsSmartleasingExpenseLock();
        expenseLock.setLockMonth(expenseMonth);
        expenseLock.setLockUser(currency.getUserName());
        expenseLock.setLockTime(new Date());
        expenseLock.setFkAdministrativeOfficeId(managementOffice);
        expenseLock.setFkPropertyCompanyId(property);
        expenseLockMapper.insert(expenseLock);
        // 生成当月费用
        if(DateUtil.format(DateUtil.lastMonth(),"yyyyMM").equals(expenseMonth)) {
            generateExpenseInformationTask.generateExpenseInformation();
        }
        return Result.success();
    }

    @Override
    public void downloadTemplateFile(HttpServletResponse response,String token) {
        try {
            //得到登录人id
            Currency currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getSignature());
            if (null == currency) {
                currency = (Currency) redisTemplate.opsForValue().get("ccms:" + JWT.decode(token).getClaim("phone").asString());
            }
            String property = currency.getProperty();
            if (StringUtils.isBlank(property)) {
                property = null;
            }
            String managementOffice = currency.getManagementOffice();
            if (StringUtils.isBlank(managementOffice)) {
                managementOffice = null;
            }
            // 上次扎帐月度
            String lastStatisticsMonth = expenseLockMapper.selectMaxMonth(managementOffice, property);
            if (StringUtils.isNotBlank(lastStatisticsMonth)) {
                lastStatisticsMonth = DateUtil.format(DateUtil.offsetMonth(new Date(), -1), "yyyyMM");
            }
            String statisticsMonth = DateUtil.format(DateUtil.offsetMonth(DateUtil.parse(lastStatisticsMonth, "yyyyMM"), 1), "yyyyMM");

            //查询待抄表数据
            List<ExpenseImport> expenseImports = this.expenseManagMapper.queryNoMeterData(statisticsMonth, lastStatisticsMonth);

            //生成Excel
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(statisticsMonth);
            HSSFRow columnNameRow = sheet.createRow(0);
            columnNameRow.createCell(0).setCellValue("序号");
            columnNameRow.createCell(1).setCellValue("单元编码");
            columnNameRow.createCell(2).setCellValue("水费编码");
            columnNameRow.createCell(3).setCellValue("电费编码");
            columnNameRow.createCell(4).setCellValue("气费编码");
            columnNameRow.createCell(5).setCellValue("本月水行度");
            columnNameRow.createCell(6).setCellValue("本月电行度");
            columnNameRow.createCell(7).setCellValue("本月气行度");
            columnNameRow.createCell(8).setCellValue("上月水行度");
            columnNameRow.createCell(9).setCellValue("上月电行度");
            columnNameRow.createCell(10).setCellValue("上月气行度");
            columnNameRow.createCell(11).setCellValue("热水费编码");
            columnNameRow.createCell(12).setCellValue("上月热水行度");
            columnNameRow.createCell(13).setCellValue("本月热水行度");
            int rowNum = 1;
            if (expenseImports != null) {
                for (ExpenseImport ei : expenseImports) {
                    HSSFRow row = sheet.createRow(rowNum);
                    row.createCell(0).setCellValue(rowNum);
                    row.createCell(1).setCellValue(ei.getUnitCoding());
                    row.createCell(2).setCellValue(ei.getWaterCostCode());
                    row.createCell(3).setCellValue(ei.getElectricCostCode());
                    row.createCell(4).setCellValue(ei.getCoalGasCostCode());
                    row.createCell(5).setCellValue(ei.getMonthWaterMobility());
                    row.createCell(6).setCellValue(ei.getMonthElectricityMobility());
                    row.createCell(7).setCellValue(ei.getMonthGasMobility());
                    row.createCell(8).setCellValue(ei.getLastMonthWaterMobility());
                    row.createCell(9).setCellValue(ei.getLastMonthElectricityMobility());
                    row.createCell(10).setCellValue(ei.getLastMonthGasMobility());
                    row.createCell(11).setCellValue(ei.getHotWaterCostCode());
                    row.createCell(12).setCellValue(ei.getLastMonthHotWaterMobility());
                    row.createCell(13).setCellValue(ei.getMonthHotWaterMobility());
                    rowNum++;
                }
            }
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename="+statisticsMonth+".xls");
            response.setContentType("application/vnd.ms-excel");
            //response.setHeader("Content-length", "" + workbook.getBytes().length);
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            workbook.write(toClient);
            toClient.flush();
            toClient.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private List<CcmsSmartleasingExpenseEntry> getEntrys(MultipartFile file){
        List<CcmsSmartleasingExpenseEntry> entries = new ArrayList<>();
        int rowNum = 0;//已取值的行数
        int realRowCount = 0;//真正有数据的行数

        //得到工作空间
        Workbook workbook = null;
        try {
            workbook = ExcelUtils.getWorkbookByInputStream(file.getInputStream(), file.getOriginalFilename());
        } catch (Exception e) {
            throw new RuntimeException("上传文件格式错误");
        }
        if(workbook == null){
            throw new RuntimeException("上传文件格式错误");
        }
        if(workbook != null){
            //得到工作表
            Sheet sheet = ExcelUtils.getSheetByWorkbook(workbook, 0);
            
            realRowCount = sheet.getPhysicalNumberOfRows();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for(Row row:sheet) {
                if(realRowCount == rowNum) {
                    break;
                }
                if(ExcelUtils.isBlankRow(row)) {//空行跳过
                    continue;
                }
                if(row.getRowNum() == -1) {
                    continue;
                }else {
                    if(row.getRowNum() == 0) {//第一行表头跳过
                        continue;
                    }
                }
                rowNum ++;
                int colNum = 0;
                try {
                    CcmsSmartleasingExpenseEntry entry = new CcmsSmartleasingExpenseEntry();
                    String roomCode = ExcelUtils.getCellValue(sheet,row,1);
                    if(StringUtils.isEmpty(roomCode)){
                        throw new RuntimeException("房间号输入错误，行号：" + rowNum);
                    }
                    String yearMth = sheet.getSheetName();
                    CcmsSmartleasingExpenseEntry retEntry = expenseEntryMapper.selectEntryByRoomCode(roomCode,yearMth);
                    if(retEntry == null || StringUtils.isEmpty(retEntry.getPkExpenseEntryId())){
                        throw new RuntimeException("房间无当月费用，行号：" + rowNum);
                    }
                    CcmsSmartleasingExpenseManag expenseManag = expenseManagMapper.getMeterReadingByExpenseManagId(retEntry.getFkExpenseManagId(),null,null);
                    if(StringUtils.isNotBlank(expenseManag.getPaymentStatus())){
                        continue;
                    }
                    entry.setPkExpenseEntryId(retEntry.getPkExpenseEntryId());
                    entry.setFkRoomId(retEntry.getFkRoomId());
                    entry.setFkExpenseManagId(retEntry.getFkExpenseManagId());
                    entry.setMonthWaterMobility(getBigDecimalValue(ExcelUtils.getCellValue(sheet,row,5)));
                    entry.setMonthElectricityMobility(getBigDecimalValue(ExcelUtils.getCellValue(sheet,row,6)));
                    entry.setMonthGasMobility(getBigDecimalValue(ExcelUtils.getCellValue(sheet,row,7)));
                    entry.setLastMonthWaterMobility(getBigDecimalValue(ExcelUtils.getCellValue(sheet,row,8)));
                    entry.setLastMonthElectricityMobility(getBigDecimalValue(ExcelUtils.getCellValue(sheet,row,9)));
                    entry.setLastMonthGasMobility(getBigDecimalValue(ExcelUtils.getCellValue(sheet,row,10)));
                    entry.setMonthHotWaterMobility(getBigDecimalValue(ExcelUtils.getCellValue(sheet,row,11)));

                    entry.setMonthWaterConsumption(comsumption(entry.getMonthWaterMobility(),entry.getLastMonthWaterMobility()));
                    entry.setMonthElectricityConsumption(comsumption(entry.getMonthElectricityMobility(),entry.getLastMonthElectricityMobility()));
                    entry.setMonthGasConsumption(comsumption(entry.getMonthGasMobility(),entry.getLastMonthGasMobility()));
                    entry.setOtherPrice(new BigDecimal("0"));
                    entry.setComprehensiveManagementRate(new BigDecimal("0"));
                    entry.setGasArrears(new BigDecimal("0"));
                    entry.setGasChangeWatchSpense(new BigDecimal("0"));
                    entry.setGasShortage(new BigDecimal("0"));

                    entries.add(entry);
                } catch (Exception e) {
                    log.error("poi read excel error" ,e);
                    throw new RuntimeException(String.format("第"+rowNum+"行 第"+colNum+"列 数据错误"));
                }
            }
        }
        return entries;
    }

    private BigDecimal comsumption(BigDecimal monthValue,BigDecimal lastValue){
        if(monthValue == null || lastValue == null){
            return monthValue == null ? new BigDecimal("0") : monthValue;
        }else{
           return monthValue.subtract(lastValue);
        }
    }

    private BigDecimal getBigDecimalValue(String value){
        if(StringUtils.isBlank(value)){
            return null;
        }else{
            return new BigDecimal(value);
        }
    }


    //public Result meterReadingReview(CcmsSmartleasingExpenseEntry expenseEntry, String token) {

    @Override
    public void exportSummaryReport(HttpServletResponse response,String startDate,String endDate,List<String> areaNumberIds,String token) {
        try{
            int rowNum =0 ;

            //生成Excel
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("收入明细表");
            HSSFCellStyle cellStyle= workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            HSSFRow tableNameRow = sheet.createRow(rowNum);
            HSSFCell cell=tableNameRow.createCell(0);
            cell.setCellValue("汇总表");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,26));
            rowNum++;
            HSSFRow dateRow = sheet.createRow(rowNum);
            dateRow.createCell(0).setCellValue("日期:("+startDate+"-"+endDate+")"); //todo
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,1));
            rowNum++;

            HSSFRow columnNameRow = sheet.createRow(rowNum);
            cell=columnNameRow.createCell(0);
            cell.setCellValue("物业");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,0,0));

            cell=columnNameRow.createCell(1);
            cell.setCellValue("房租");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,1,3));


            cell=columnNameRow.createCell(4);
            cell.setCellValue("管理费");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,4,6));

            cell=columnNameRow.createCell(7);
            cell.setCellValue("水费");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,7,9));

            cell=columnNameRow.createCell(10);
            cell.setCellValue("电费");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,10,12));

            cell=columnNameRow.createCell(13);
            cell.setCellValue("电梯费");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,13,15));

            cell=columnNameRow.createCell(16);
            cell.setCellValue("卫生费");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,16,18));

            cell=columnNameRow.createCell(19);
            cell.setCellValue("代管费");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,19,21));

            cell=columnNameRow.createCell(22);
            cell.setCellValue("收入合计");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,22,22));
            cell=columnNameRow.createCell(23);
            cell.setCellValue("税额合计");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,23,23));
            cell=columnNameRow.createCell(24);
            cell.setCellValue("发票合计");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,24,24));
            cell=columnNameRow.createCell(25);
            cell.setCellValue("往来总计");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,25,25));
            cell=columnNameRow.createCell(26);
            cell.setCellValue("财务入账总计");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,26,26));

            rowNum++;

            columnNameRow = sheet.createRow(rowNum++);
            cell=columnNameRow.createCell(1);
            cell.setCellValue("租金");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(2);
            cell.setCellValue("收入");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(3);
            cell.setCellValue("税额");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(4);
            cell.setCellValue("管理费");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(5);
            cell.setCellValue("收入");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(6);
            cell.setCellValue("税额");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(7);
            cell.setCellValue("水费");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(8);
            cell.setCellValue("收入");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(9);
            cell.setCellValue("税额");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(10);
            cell.setCellValue("电费");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(11);
            cell.setCellValue("收入");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(12);
            cell.setCellValue("税额");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(13);
            cell.setCellValue("电梯费");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(14);
            cell.setCellValue("收入");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(15);
            cell.setCellValue("税额");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(16);
            cell.setCellValue("卫生费");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(17);
            cell.setCellValue("收入");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(18);
            cell.setCellValue("税额");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(19);
            cell.setCellValue("代管费");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(20);
            cell.setCellValue("收入");
            cell.setCellStyle(cellStyle);
            cell=columnNameRow.createCell(21);
            cell.setCellValue("税额");
            cell.setCellStyle(cellStyle);

            List<CcmsSmartleasingExpenseReport> lst1 = expenseReportMapper.getSummary(startDate,endDate,areaNumberIds);  

            BigDecimal monthlyRentTotal = new BigDecimal("0.00");
            BigDecimal monthlyRentIncomingTotal=new BigDecimal("0.00");
            BigDecimal  monthlyRentTaxTotal=new BigDecimal("0.00");
            BigDecimal manageFeeTotal=new BigDecimal("0.00");
            BigDecimal manageFeeIncomingTotal=new BigDecimal("0.00");
            BigDecimal manageFeeTaxTotal=new BigDecimal("0.00");
            BigDecimal  waterFeeTotal=new BigDecimal("0.00");
            BigDecimal waterFeeIncomingTotal=new BigDecimal("0.00");
            BigDecimal waterFeeTaxTotal=new BigDecimal("0.00");
            BigDecimal electricFeeTotal=new BigDecimal("0.00");
            BigDecimal electricFeeIncomingTotal=new BigDecimal("0.00");
            BigDecimal electricFeeTaxTotal=new BigDecimal("0.00");
            BigDecimal elevatorFeeTotal=new BigDecimal("0.00");
            BigDecimal elevatorFeeIncomingTotal=new BigDecimal("0.00");
            BigDecimal elevatorFeeTaxTotal=new BigDecimal("0.00");
            BigDecimal sanitaryFeeTotal=new BigDecimal("0.00");
            BigDecimal sanitaryFeeIncomingTotal=new BigDecimal("0.00");
            BigDecimal sanitaryFeeTaxTotal=new BigDecimal("0.00");

            BigDecimal incomingTotalTotal=new BigDecimal("0.00");
            BigDecimal taxTotalTotal=new BigDecimal("0.00");
            BigDecimal invoicedTotalTotal=new BigDecimal("0.00");
            BigDecimal currentTotalTotal=new BigDecimal("0.00");
            BigDecimal inAccountTotalTotal=new BigDecimal("0.00");

            HSSFRow rowData = sheet.createRow(rowNum++);

            if (lst1 != null) {
                BigDecimal bd = new BigDecimal("0.00");
                String s1="";
                for (CcmsSmartleasingExpenseReport em:lst1) {
                    s1 = em.getAreaName();
                    rowData.createCell(0).setCellValue(s1);

                    bd = em.getMonthlyRent();
                    rowData.createCell(1).setCellValue(bd.toString());
                    monthlyRentTotal = monthlyRentTotal.add(bd);

                    bd = em.getMonthlyRentIncoming();
                    rowData.createCell(2).setCellValue(bd.toString());
                    monthlyRentIncomingTotal = monthlyRentIncomingTotal.add(bd);

                    bd = em.getMonthlyRentTax();
                    rowData.createCell(3).setCellValue(bd.toString());
                    monthlyRentTaxTotal = monthlyRentTaxTotal.add(bd);

                    bd = em.getManageFee();
                    rowData.createCell(4).setCellValue(bd.toString());
                    manageFeeTotal = manageFeeTotal.add(bd);

                    bd = em.getManageFeeIncoming();
                    rowData.createCell(5).setCellValue(bd.toString());
                    manageFeeIncomingTotal = manageFeeIncomingTotal.add(bd);

                    bd = em.getManageFeeTax();
                    rowData.createCell(6).setCellValue(bd.toString());
                    manageFeeTaxTotal = manageFeeTaxTotal.add(bd);

                    bd = em.getWaterFee();
                    rowData.createCell(7).setCellValue(bd.toString());
                    waterFeeTotal = waterFeeTotal.add(bd);

                    bd = em.getWaterFeeIncoming();
                    rowData.createCell(8).setCellValue(bd.toString());
                    waterFeeIncomingTotal = waterFeeIncomingTotal.add(bd);

                    bd = em.getWaterFeeTax();
                    rowData.createCell(9).setCellValue(bd.toString());
                    waterFeeTaxTotal = waterFeeTaxTotal.add(bd);

                    bd = em.getElectricFee();
                    rowData.createCell(10).setCellValue(bd.toString());
                    electricFeeTotal = electricFeeTotal.add(bd);

                    bd = em.getElectricFeeIncoming();
                    rowData.createCell(11).setCellValue(bd.toString());
                    electricFeeIncomingTotal = electricFeeIncomingTotal.add(bd);

                    bd = em.getElectricFeeTax();
                    rowData.createCell(12).setCellValue(bd.toString());
                    electricFeeTaxTotal = electricFeeTaxTotal.add(bd);

                    bd = em.getElevatorFee();
                    rowData.createCell(13).setCellValue(bd.toString());
                    elevatorFeeTotal = elevatorFeeTotal.add(bd);

                    bd = em.getElevatorFeeIncoming();
                    rowData.createCell(14).setCellValue(bd.toString());
                    elevatorFeeIncomingTotal = elevatorFeeIncomingTotal.add(bd);

                    bd = em.getElevatorFeeTax();
                    rowData.createCell(15).setCellValue(bd.toString());
                    elevatorFeeTaxTotal = elevatorFeeTaxTotal.add(bd);

                    bd = em.getSanitaryFee();
                    rowData.createCell(16).setCellValue(bd.toString());
                    sanitaryFeeTotal = sanitaryFeeTotal.add(bd);

                    bd = em.getSanitaryFeeIncoming();
                    rowData.createCell(17).setCellValue(bd.toString());
                    sanitaryFeeIncomingTotal = sanitaryFeeIncomingTotal.add(bd);

                    bd = em.getSanitaryFeeTax();
                    rowData.createCell(18).setCellValue(bd.toString());
                    sanitaryFeeTaxTotal = sanitaryFeeTaxTotal.add(bd);

                    rowData.createCell(19).setCellValue("NA");
                    rowData.createCell(20).setCellValue("NA");
                    rowData.createCell(21).setCellValue("NA");

                    bd = em.getIncomingTotal();
                    rowData.createCell(22).setCellValue(bd.toString());
                    incomingTotalTotal = incomingTotalTotal.add(bd);

                    bd = em.getTaxTotal();
                    rowData.createCell(23).setCellValue(bd.toString());
                    taxTotalTotal = taxTotalTotal.add(bd);

                    bd = em.getInvoicedTotal();
                    rowData.createCell(24).setCellValue(bd.toString());
                    invoicedTotalTotal = invoicedTotalTotal.add(bd);

                    bd = em.getCurrentTotal();
                    rowData.createCell(25).setCellValue(bd.toString());
                    currentTotalTotal = currentTotalTotal.add(bd);

                    bd = em.getInAccountTotal();
                    rowData.createCell(26).setCellValue(bd.toString());
                    inAccountTotalTotal = inAccountTotalTotal.add(bd);

                    rowData = sheet.createRow(rowNum++);
                }
            }

            //whether list contains row or not, rowData already created for Summary
            cell = rowData.createCell(0);
            cell.setCellValue("合计");
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(1);
            cell.setCellValue(monthlyRentTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(2);
            cell.setCellValue(monthlyRentIncomingTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(3);
            cell.setCellValue(monthlyRentTaxTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(4);
            cell.setCellValue(manageFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(5);
            cell.setCellValue(manageFeeIncomingTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(6);
            cell.setCellValue(manageFeeTaxTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(7);
            cell.setCellValue(waterFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(8);
            cell.setCellValue(waterFeeIncomingTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(9);
            cell.setCellValue(waterFeeTaxTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(10);
            cell.setCellValue(electricFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(11);
            cell.setCellValue(electricFeeIncomingTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(12);
            cell.setCellValue(electricFeeTaxTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(13);
            cell.setCellValue(elevatorFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(14);
            cell.setCellValue(elevatorFeeIncomingTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(15);
            cell.setCellValue(elevatorFeeTaxTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(16);
            cell.setCellValue(sanitaryFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(17);
            cell.setCellValue(sanitaryFeeIncomingTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(18);
            cell.setCellValue(sanitaryFeeTaxTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(22);
            cell.setCellValue(incomingTotalTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(23);
            cell.setCellValue(taxTotalTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(24);
            cell.setCellValue(invoicedTotalTotal.toString());
            cell.setCellStyle(cellStyle);
            
            cell = rowData.createCell(25);
            cell.setCellValue(currentTotalTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = rowData.createCell(26);
            cell.setCellValue(inAccountTotalTotal.toString());
            cell.setCellStyle(cellStyle);


            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename="+new String("费用管理-汇总表".getBytes(),"ISO8859-1")+System.currentTimeMillis()+".xls");
            response.setContentType("application/vnd.ms-excel");
            //response.setHeader("Content-length", "" + workbook.getBytes().length);
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            workbook.write(toClient);
            toClient.flush();
            toClient.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void exportSummaryReportByClient(HttpServletResponse response, String startDate,String endDate,List<String> areaNumberIds,String token) {
        try{
            int rowNum = 0;

            //生成Excel
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("客户分类明细表");
            HSSFCellStyle cellStyle= workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            HSSFRow tableNameRow = sheet.createRow(0);
            HSSFCell cell=tableNameRow.createCell(rowNum);
            cell.setCellValue("客户分类汇总表");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,20));

            rowNum++;
            HSSFRow secondRow = sheet.createRow(rowNum);
            secondRow.createCell(0).setCellValue("部门：房产经营部");
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,4));                    
            secondRow.createCell(5).setCellValue("日期("+startDate+"-"+endDate+")");
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,5,20));        

            rowNum++;
            HSSFRow columnNameRow = sheet.createRow(rowNum);
            cell=columnNameRow.createCell(0);
            cell.setCellValue("序号");
            cell.setCellStyle(cellStyle); 

            cell=columnNameRow.createCell(1);
            cell.setCellValue("租户");
            cell.setCellStyle(cellStyle);   

            cell=columnNameRow.createCell(2);
            cell.setCellValue("物业");
            cell.setCellStyle(cellStyle);      

            cell=columnNameRow.createCell(3);
            cell.setCellValue("水费");
            cell.setCellStyle(cellStyle);   

            cell=columnNameRow.createCell(4);
            cell.setCellValue("电费");
            cell.setCellStyle(cellStyle);  

            cell=columnNameRow.createCell(5);
            cell.setCellValue("煤气费");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(6);
            cell.setCellValue("水电气");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(7);
            cell.setCellValue("房租");
            cell.setCellStyle(cellStyle); 

            cell=columnNameRow.createCell(8);
            cell.setCellValue("卫生费");
            cell.setCellStyle(cellStyle);  

            cell=columnNameRow.createCell(9);
            cell.setCellValue("其他租金");
            cell.setCellStyle(cellStyle);   
            
            cell=columnNameRow.createCell(10);
            cell.setCellValue("电梯费");
            cell.setCellStyle(cellStyle);    

            cell=columnNameRow.createCell(11);
            cell.setCellValue("电梯延时");
            cell.setCellStyle(cellStyle);    

            cell=columnNameRow.createCell(12);
            cell.setCellValue("发票房租");
            cell.setCellStyle(cellStyle);    

            cell=columnNameRow.createCell(13);
            cell.setCellValue("电话费");
            cell.setCellStyle(cellStyle);   

            cell=columnNameRow.createCell(14);
            cell.setCellValue("管理费");
            cell.setCellStyle(cellStyle);  

            cell=columnNameRow.createCell(15);
            cell.setCellValue("维修费");
            cell.setCellStyle(cellStyle);  

            cell=columnNameRow.createCell(16);
            cell.setCellValue("垃圾费");
            cell.setCellStyle(cellStyle);  
            
            cell=columnNameRow.createCell(17);
            cell.setCellValue("排水费");
            cell.setCellStyle(cellStyle);  
            
            cell=columnNameRow.createCell(18);
            cell.setCellValue("有线电视");
            cell.setCellStyle(cellStyle);               
            
            cell=columnNameRow.createCell(19);
            cell.setCellValue("煤保");
            cell.setCellStyle(cellStyle);              

            cell=columnNameRow.createCell(20);
            cell.setCellValue("其它");
            cell.setCellStyle(cellStyle);  

            cell=columnNameRow.createCell(21);
            cell.setCellValue("收据合计");
            cell.setCellStyle(cellStyle);              

            cell=columnNameRow.createCell(22);
            cell.setCellValue("金额");
            cell.setCellStyle(cellStyle);     
            
            cell=columnNameRow.createCell(23);
            cell.setCellValue("备注");
            cell.setCellStyle(cellStyle);              

            BigDecimal  waterFeeTotal=new BigDecimal("0.00");
            BigDecimal  electricFeeTotal=new BigDecimal("0.00");
            BigDecimal  gasFeeTotal=new BigDecimal("0.00");
            BigDecimal  waterElectricGasFeeTotal=new BigDecimal("0.00");
            BigDecimal  monthlyRentTotal=new BigDecimal("0.00");
            BigDecimal  sanitaryFeeTotal=new BigDecimal("0.00");
            BigDecimal  otherFeeTotal=new BigDecimal("0.00");
            BigDecimal  elevatorFeeTotal=new BigDecimal("0.00");
            BigDecimal  telephoneFeeTotal=new BigDecimal("0.00");
            BigDecimal  manageFeeTotal=new BigDecimal("0.00");
            BigDecimal  maintenanceFundTotal=new BigDecimal("0.00");
            BigDecimal  FeeTotal=new BigDecimal("0.00");
            BigDecimal  trashHandlingFeeTotal=new BigDecimal("0.00");
            BigDecimal  drainageFeeTotal=new BigDecimal("0.00");
            BigDecimal  gasInsuranceTotal=new BigDecimal("0.00");
            BigDecimal  totalAmountTotal=new BigDecimal("0.00");

            List<CcmsSmartleasingExpenseReport> lst1 = expenseReportMapper.getSummaryByClient(startDate,endDate,areaNumberIds);

            rowNum++;
            HSSFRow dataRow = sheet.createRow(rowNum++);

            if (lst1 != null) {
                BigDecimal bd = new BigDecimal("0.00");
                String s1="";
                int i=1;

                for (CcmsSmartleasingExpenseReport em:lst1){

                    dataRow.createCell(0).setCellValue(i++);

                    s1 = em.getTenantName();
                    dataRow.createCell(1).setCellValue(s1);

                    s1= em.getHouseNumber();
                    dataRow.createCell(2).setCellValue(s1);

                    bd= em.getWaterFee();
                    dataRow.createCell(3).setCellValue(bd.toString());
                    waterFeeTotal = waterFeeTotal.add(bd);

                    bd = em.getElectricFee();
                    dataRow.createCell(4).setCellValue(bd.toString());
                    electricFeeTotal = electricFeeTotal.add(bd);

                    bd=em.getGasFee();
                    dataRow.createCell(5).setCellValue(bd.toString());
                    gasFeeTotal = gasFeeTotal.add(bd);

                    bd = em.getWaterElectricGasFee();
                    dataRow.createCell((6)).setCellValue(bd.toString());
                    waterElectricGasFeeTotal = waterElectricGasFeeTotal.add(bd);

                    bd = em.getMonthlyRent();
                    dataRow.createCell(7).setCellValue(bd.toString());
                    monthlyRentTotal = monthlyRentTotal.add(bd);

                    bd = em.getSanitaryFee();
                    dataRow.createCell(8).setCellValue(bd.toString());
                    sanitaryFeeTotal = sanitaryFeeTotal.add(bd);

                    bd = em.getOtherFee();
                    dataRow.createCell(9).setCellValue(bd.toString());
                    otherFeeTotal = otherFeeTotal.add(bd);

                    bd =em.getElevatorFee();
                    dataRow.createCell(10).setCellValue(bd.toString());
                    elevatorFeeTotal =elevatorFeeTotal.add(bd);

                    dataRow.createCell(11).setCellValue("NA");
                    dataRow.createCell(12).setCellValue("NA");

                    bd =em.getTelephoneFee();
                    dataRow.createCell(13).setCellValue(bd.toString());
                    telephoneFeeTotal= telephoneFeeTotal.add(bd);

                    bd= em.getManageFee();
                    dataRow.createCell(14).setCellValue(bd.toString());
                    manageFeeTotal = manageFeeTotal.add(bd);

                    bd = em.getMaintenanceFund();
                    dataRow.createCell(15).setCellValue(bd.toString());
                    maintenanceFundTotal = maintenanceFundTotal.add(bd);

                    bd =em.getTrashHandlingFee();
                    dataRow.createCell(16).setCellValue(bd.toString());
                    trashHandlingFeeTotal = trashHandlingFeeTotal.add(bd);

                    bd = em.getDrainageFee();
                    dataRow.createCell(17).setCellValue(bd.toString());
                    drainageFeeTotal = drainageFeeTotal.add(bd);

                    dataRow.createCell(18).setCellValue("NA");

                    bd = em.getGasInsurance();
                    dataRow.createCell(19).setCellValue(bd.toString());
                    gasInsuranceTotal = gasInsuranceTotal.add(bd);
                    
                    dataRow.createCell(20).setCellValue("NA");
                    dataRow.createCell(21).setCellValue("NA");

                    bd = em.getTotalAmount();
                    dataRow.createCell(22).setCellValue(bd.toString());
                    totalAmountTotal = totalAmountTotal.add(bd);
                
                    dataRow = sheet.createRow(rowNum++);
                }

            }

            cell = dataRow.createCell(0);
            cell.setCellValue("合计");
            cell.setCellStyle(cellStyle);

            dataRow.createCell(1).setCellValue("");
            dataRow.createCell(2).setCellValue("");

            cell = dataRow.createCell(3);
            cell.setCellValue(waterFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(4);
            cell.setCellValue(electricFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(5);
            cell.setCellValue(gasFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(6);
            cell.setCellValue(waterElectricGasFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(7);
            cell.setCellValue(monthlyRentTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(8);
            cell.setCellValue(sanitaryFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(9);
            cell.setCellValue(otherFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(10);
            cell.setCellValue(elevatorFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(13);
            cell.setCellValue(telephoneFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(14);
            cell.setCellValue(manageFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(15);
            cell.setCellValue(maintenanceFundTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(16);
            cell.setCellValue(trashHandlingFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(17);
            cell.setCellValue(drainageFeeTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(19);
            cell.setCellValue(gasInsuranceTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(22);
            cell.setCellValue(totalAmountTotal.toString());
            cell.setCellStyle(cellStyle);

            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename="+new String("费用管理-客户分类汇总表".getBytes(),"ISO8859-1")+System.currentTimeMillis()+".xls");
            response.setContentType("application/vnd.ms-excel");
            //response.setHeader("Content-length", "" + workbook.getBytes().length);
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            workbook.write(toClient);
            toClient.flush();
            toClient.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void exportDebtDetailReport(HttpServletResponse response, String startDate, List<String> areaNumberIds,String token) {
        try{
            //生成Excel
            int rowNum=0, iMonth=0;

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("欠款明细");
            HSSFCellStyle cellStyle= workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            HSSFRow tableNameRow = sheet.createRow(0);
            HSSFCell cell=tableNameRow.createCell(rowNum);
            cell.setCellValue(startDate.substring(0,4)+"年"+startDate.substring(4,6)+"月应收账款余额");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,9));

            HSSFRow columnNameRow = sheet.createRow(rowNum+2); //leave 1 blank line
            cell=columnNameRow.createCell(0);
            cell.setCellValue("序号");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(1);
            cell.setCellValue("楼名");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(2);
            cell.setCellValue("客户名");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(3);
            cell.setCellValue("房号");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(4);
            cell.setCellValue("楼名+房号");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(5);
            iMonth = Integer.valueOf(startDate.substring(4,6));
            if (iMonth==1)
                cell.setCellValue("12月欠款");
            else
                cell.setCellValue((iMonth-1)+"月欠款");


            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(6);
            cell.setCellValue(iMonth+"月新增");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(7);
            cell.setCellValue(iMonth+"月回款");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(8);
            cell.setCellValue(iMonth+"月末欠款");
            cell.setCellStyle(cellStyle);

            cell=columnNameRow.createCell(9);
            cell.setCellValue("备注");
            cell.setCellStyle(cellStyle);

            BigDecimal bd = new BigDecimal("0.00");
            BigDecimal lastMonthDebtTotal = new BigDecimal("0.00");
            BigDecimal thisMonthDebtTotal  = new BigDecimal("0.00");
            BigDecimal thisMonthPayTotal = new BigDecimal("0.00");
            BigDecimal thisMonthDebtPlusBeforeTotal = new BigDecimal("0.00");

            List<CcmsSmartleasingExpenseReport> lst1 = expenseReportMapper.getDebtDetail(startDate,areaNumberIds);
            rowNum = rowNum+3;
            HSSFRow dataRow = sheet.createRow(rowNum++);

            if (lst1 != null) {
                int i=1;
                String s1="";
                
                for (CcmsSmartleasingExpenseReport em:lst1){
                    dataRow.createCell(0).setCellValue(i++);

                    s1 = em.getTenantName();
                    dataRow.createCell(1).setCellValue(s1);

                    s1 = em.getBuildingName();
                    dataRow.createCell(2).setCellValue(s1);;

                    s1 = em.getHouseNumber();
                    dataRow.createCell(3).setCellValue(s1);

                    s1 = em.getTenantName()+em.getBuildingName()+s1;
                    dataRow.createCell(4).setCellValue(s1);

                    bd = em.getLastMonthDebtPlusBefore();
                    dataRow.createCell(5).setCellValue(bd.toString());
                    lastMonthDebtTotal = lastMonthDebtTotal.add(bd);

                    bd = em.getThisMonthDebt();
                    dataRow.createCell(6).setCellValue(bd.toString());
                    thisMonthDebtTotal = thisMonthDebtTotal.add(bd);

                    bd = em.getThisMonthPay();
                    dataRow.createCell(7).setCellValue(bd.toString());
                    thisMonthPayTotal = thisMonthPayTotal.add(bd);

                    bd = em.getLastMonthDebtPlusBefore().add(em.getThisMonthDebt()).subtract(em.getThisMonthPay());
                    dataRow.createCell(8).setCellValue(bd.toString());
                    thisMonthDebtPlusBeforeTotal = thisMonthDebtPlusBeforeTotal.add(bd);

                    dataRow = sheet.createRow(rowNum++);
                }
            }

            cell = dataRow.createCell(0);
            cell.setCellValue("合计");
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(5);
            cell.setCellValue(lastMonthDebtTotal.toString());
            cell.setCellStyle(cellStyle);

            cell= dataRow.createCell(6);
            cell.setCellValue(thisMonthDebtTotal.toString());
            cell.setCellStyle(cellStyle);

            cell = dataRow.createCell(7);
            cell.setCellValue(thisMonthPayTotal.toString());
            cell.setCellStyle(cellStyle);

            cell= dataRow.createCell(8);
            cell.setCellValue(thisMonthDebtPlusBeforeTotal.toString());
            cell.setCellStyle(cellStyle);

            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename="+new String("欠款明细表".getBytes(),"ISO8859-1")+System.currentTimeMillis()+".xls");
            response.setContentType("application/vnd.ms-excel");
            //response.setHeader("Content-length", "" + workbook.getBytes().length);
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            workbook.write(toClient);
            toClient.flush();
            toClient.close();
        } catch (IOException e) {
            e.printStackTrace();
        }        
    }
}

